Value and Select Insert statement failure

[tavie]

Hello
I am trying to do the following and continue to fail....

insert into table1
(column1,column2,column3,column4, column5, column6, column7)
Values
('value','value','value','value','value',
(Select column1, column2
from table2 where column3 = "1')

I can't seem to get the syntax to work, It will work propely if I use Values or Select but not both....Thanks in advance for any help.....

 

[JamesLean]

You can't do that. You'll have to grab the result of the subquery into a variable and then use that in your insert:

DECLARE @c1 int,
  @c2 int

SELECT @c1 = column1, @c2 = column2
FROM table2
WHERE column3 = '1'

insert into table1
(column1,column2,column3,column4, column5, column6, column7)
Values 
('value','value','value','value','value',@c1, @c2)

(This assumes that your subquery only returns one row.)

--James

 

[tavie]

What if it returns multiple rows....???

 

[sunila7]

Hi,

Try this... Hope this is wht ur looking for...

insert into table1
(column1,column2,column3,column4, column5, column6, column7)

(Select 'value','value','value','value','value',
column1, column2
from table2 where column3 = '1')


Sunil

 

[tavie]

Sunil....I do not believe that will work...I keep getting syntax errors because I would assume that the select statement is looking for a field called 'value'...???

 

[puredesi5]

If you put single quote(') around the 'value', It treats it as string.

 

[puredesi5]

It works as Sunil mentions. I tried it and it's working.

 

[tavie]

Puredesi5...Thank you that seemed to work. Next problem....I have a PK contraint on the first column...How would I go about inserting multiple records into the table and give each record it's own GUID?????

 

[JamesLean]

Use the NEWID() function:

insert into table1
(column1,column2,column3,column4, column5, column6, column7)
 
(Select NEWID(),'value','value','value','value',
 column1, column2
from table2 where column3 = '1')

--James