Execute – Insert into …. error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Execute – Insert into …. error

Where is the problem ? I need dynamicly insert data to table. When i use this part of code i have an error. When @pole1 begin with some letter , i have this message Server: Msg 128, Level 15, State 1, Line 1
The name ‘a’ is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Declare @Pole1 varchar, @Pole2 varchar(255), @sqlcommand varchar(255), @tmp_table varchar(255)
Set @tmp_table=’tempor’ IF OBJECT_ID(@tmp_table)IS not NULL
Set @sqlcommand=’Drop table ‘ + @tmp_table
Execute (@sqlcommand) Set @sqlcommand =’create table ‘ + @tmp_table+’ ( Col1 varchar(255),Col2 varchar(255) )’
Execute (@sqlcommand)
Set @Pole1=’abc123456′
Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1 ) Values( ‘+ @pole1+’)’
Execute (@sqlCommand)
Thanks, Lubo

If you would enter this command as a straight query in QA, it would read:
INSERT Into MyTmpTable (COl1) Values (‘abc123456’) When creating dynamic SQL, your script has to give two quote marks for each quote mark that needs to be in the straight query. Plus, to mark the boundary between the literal string and the rest of your script, you need another quote mark – like this: Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1 ) Values( ”’ + @pole1 + ”’)’ Someone will soon come around with the usual warnings abot dynamic SQL …
Hi Lubo, make sure that the value is enclosed in single quotes Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1 ) Values( ”’+ @pole1+”’)’ Cheers
Thakns, but where there are 2 or more columns and values what i want to wrint there?
Something like this Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1,col2,col3 )
Values( ”’+ @pole1+”’,”’+ @pole2+”’,”’+ @pole3+”’)’
Madhivanan Failing to plan is Planning to fail
Many, many thanks . Lubo
But It is better to avoid Dynamic SQL in this case
Why do you want to create the table dynamically?
Madhivanan Failing to plan is Planning to fail
Please avoid opening two threads on the very same question!
Seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9620 It makes it really hard to track who answered what where already. —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)