syntax error on INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

syntax error on INSERT

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-05-27 : 10:02:12

Background:
I need to create some new records using data from old serial numbers. After this process has completed, I will mark the old serial numbers as invalid (we keep a paper trail; we do not delete the records).

I am trying to use this basic INSERT statement, but it does not seem to like me using a SELECT statement in the VALUES section:

DECLARE @OldNumber char(20), @NewNumber char(20)
SET @OldNumber='CP21253 1015 09' -- 'CP21253 1018 09''CP21253 1014 09''CP21253 1009 09''CP21253 1008 09'
SET @NewNumber='CP21253 1022 09' -- 'CP21253 1021 09''CP21253 1020 09''CP21253 1019 09''CP21253 1001 09',

INSERT INTO ACP_Parts
	(OP_ID, Serial_Number, Date_Time, System_ID, Test_Result, WorkOrder_Number, WorkOrder_Qty, WorkOrder_Seq, Line_Number, CoilType, TubeWall, BrazeJoint, Supervisor)
VALUES (
	SELECT OP_ID, @NewNumber as 'Serial_Number', Date_Time, System_ID, Test_Result, WorkOrder_Number, WorkOrder_Qty, WorkOrder_Seq, Line_Number, CoilType, TubeWall, BrazeJoint, Supervisor
	FROM  ACP_Parts WHERE (Serial_Number=@OldNumber)
)

The error messages from Query Analyzer are:

quote:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near ')'.

How should I modify my INSERT command so that it accepts the SELECT statement?

Avoid Sears Home Improvement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 10:05:00

remove the VALUES keyword.

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-27 : 10:05:11

Take away "VALUES" and outer "(" and ")"

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-27 : 10:05:56

eleven seconds

No, you're never too old to Yak'n'Roll if you're too young to die.

jp2code
Posting Yak Master

175 Posts

Posted - 2009-05-27 : 10:17:58

quote:
Originally posted by webfred

eleven seconds

LOL

Amazing how helpful this site can be when I get stumped on a simple issue.

So, the "INSERT INTO" section on this site [http://www.w3schools.com/sql/sql_quickref.asp] is invalid for T-SQL?

Avoid Sears Home Improvement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 10:19:55

you either use


INSERT INTO <table> ( <column(s)>)
VALUES ( <values>)

OR

INSERT INTO <table> ( <column(s)>)
SELECT <column(s)>

not mixing both together

KH
[spoiler]Time is always against us[/spoiler]

jp2code
Posting Yak Master

175 Posts

Posted - 2009-05-27 : 10:23:07

Thanks!

I've often thought it would be neat to work in Singapore or India, but with the fantastic minds I encounter on here showing those locations, I know to stay where I am.

~Joe

Avoid Sears Home Improvement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 10:28:37

it because it is beginning of day where you are and you probably haven't have your cuppa yet. It's close to midnight where i am . .. and i already had over-dose of caffeine.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources