CODEWITHSUNDEEP
sql-server-2008
edgarmtze
edgarmtze

Reputation: 25038

Incorrect insert syntaxis SQL Server

I have created a Temporal Table:
create table #T_Table(id int,name nvarchar(80),value float);

I am executing dynamic SQL to insert to #T_Table the select result from @table_name like this:

SET @template = 'INSERT #T_Table (id,name,value) VALUES('+Cast(@counterI as nvarchar)+', '+ @fieldFromTableName +', Select [value] = SUM('+@fieldFromTableName+') FROM '+ @table_name +')';
EXEC (@template);

So if I print@template:
INSERT #T_Table(id,name,value) VALUES(1, x1, Select [value] = SUM(x1) FROM Mytable);

What is it wrong?

Upvotes: 0

Views: 56

Answers (2)

pcofre
pcofre

Reputation: 4066

Here´s the INSERT you should build:

INSERT #T_Table(id,name,value) 
SELECT 1, 'x1', SUM(x1) FROM Mytable

Upvotes: 1

cjk
cjk

Reputation: 46415

You can't nest a SELECT inside a VALUES set of values.

Change VALUES to SELECT and get rid of the brackets. You also need to make sure x1 is wrapped in quotes, e.g.:

INSERT #T_Table(id,name,value) 
SELECT 1, 'x1', Select [value] = SUM(x1) FROM Mytable;

To get quotes around x1 you will have to put double quotes in your quoted string to escape them: e.g. SET @quotedString = 'not quoted, ''quoted,'' not quoted'

Upvotes: 1

Related Questions