You're trying to mix the
INSERT .. SELECT
syntax with the
INSERT .. VALUES
syntax.
Even if it was allowed, that would only work if the
SELECT
returned a single value.
Try adding the date to the
SELECT
instead:
INSERT INTO test (val, trnsdate)
SELECT ROUTE, GETDATE()
FROM SHIPMENT_HEADER sh
INNER JOIN SHIPMENT_ALLOC_REQUEST a ON sh.INTERNAL_SHIPMENT_NUM = a.INTERNAL_SHIPMENT_NUM
WHERE sh.LEADING_STS >= '300'
AND sh.trailing_Sts >= '300'
AND DATEDIFF(MINUTE, a.DATE_TIME_STAMP, GETDATE()) > 20
AND sh.COMPANY = 'NonPCW'
AND ISNULL(PARTIES, '') NOT IN ('DN-SENT', 'CLosed')
AND ROUTE IS NOT NULL
AND sh.MANUALLY_ENTERED = 'N'
AND sh.route IN ('2212007086490-468', '2212007116053-44239', '2212007120681-44220')
AND ISNULL(ROUTE, '') NOT IN
(
SELECT ROUTE
FROM SHIPMENT_HEADER ssh
WHERE ssh.LEADING_STS < '300'
AND ssh.trailing_Sts < '300'
AND ssh.REJECTION_NOTE IS NULL
AND ssh.COMPANY = 'Alshaya_NonPCW'
AND ISNULL(ssh.PARTIES, '') NOT IN ('DN-SENT', 'CLosed')
AND ssh.ROUTE IS NOT NULL
AND ssh.MANUALLY_ENTERED = 'N'
)