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
Twan

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
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>