Incorrect syntax in table variable insert via select

Question

I can't figure out what in the world this is complaining about, looks fine to me:

DECLARE @newidentity int 
SET @newidentity = scope_identity()
select @newidentity

Insert into @Companies (select @newidentity, Name from Company where Name = 'Parker')

I get the following for the insert line:

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.

Martin Smith · Accepted Answer · 2012-05-27 15:20:41Z

2

Try it without brackets and specify fields of companies, please:

INSERT INTO @Companies 
            (field1, 
             field2) 
SELECT @newidentity, 
       Name 
FROM   Company 
WHERE  Name = 'Parker'

What @Companies is?

edited May 27, 2012 at 15:20

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered May 27, 2012 at 15:18

Ruben

2,5782 gold badges19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PositiveGuy Over a year ago

hmm so with table variables you can't use the shortcut of leaving out the field definitions before you specify the fields to be selected for insert?

Martin Smith Over a year ago

@CoffeeAddict - Yes you can. It is the brackets around the select that were the problem.

Collectives™ on Stack Overflow

Incorrect syntax in table variable insert via select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related