Insert values from select statement

Question

0.00/5 (No votes)

See more:

I am trying to insert values into a table "test" with the help of select statement. There are two columns in my test table named "val(varchar)" and trnsdate(varchar). But I am getting an error "

Quote:
Incorrect syntax near ','.

before getdate() function. Below the query for the same. But when I remove the trnsdate and getdate() function, the insert is working fine.

What I have tried:

INSERT INTO test (val,trnsdate) ((SELECT  ROUTE 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 DISTINCT 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')),getdate())

Posted 30-Sep-24 21:32pm

Rajath Savanth

Add a Solution

Comments

Member 16365944 3-Oct-24 7:12am

The premise of the game is simple yet addictive: Up to 60 players compete in a series of rounds, with each round qualifying for elimination until only one remains. What sets this game apart is its vibrant, almost cartoonish aesthetic, which appeals to gamers of all ages. fall guys brings a new perspective to competitive gaming, where failure is just as fun as success.

2 solutions

Solution 1

One thing that jumps out immediately, you are trying to insert two columns but you only select one column in the select statement. You have to have the same number of items in your select that you do in your insert.

Posted 30-Sep-24 21:57pm

Pete O'Hanlon

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-09-30T21:58:00

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:

SQL

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'
)