Variables in INSERT statement 1

[webuser]

Is there any way to use a variable in an Insert statement?

I am trying to do the following:

INSERT INTO Employees
ID, LastName, FirstName
SELECT ID, Last, First FROM Employees2
WHERE (...)

I would like to be able to do this:

INSERT INTO @MyTableName
@MyField1, @myField2, @MyField3
SELECT ID, Last, First FROM Employees2
WHERE (...)

Is there any way to substitute th tablename with a variable,
so that this can be run in a loop if needed where the variable would change with each iteration of the loop?
It would also be cool to be able to substitute for the field names...

Thanks in advance!

 

[jhall156]

depending on the version of SQLServer you're using you could use table variables-
DECLARE @theTable(theField1 int, theField2 varchar(255))


Then you can insert into the table variable whatever you want.

 

[tlbroadbent]

You need to create a SQL statement and execute it.

Declare @sql nvarchar(2000)

Set @sql=
'Insert ' + @tablename +
'(' + @MyField1 + ',' +
@MyField2 + ',' +
@MyField3 + ') ' +
'Select ID, Last, First ' +
'From Employees2 ' +
'Where (...)'

Exec(@sql) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jhall156]

Whoops sorry. Didn't see that you are trying to get data into database tables. So what he said. Build your SQL string and then use EXEC. I like to use CHAR(39) to embed 's in the string but you can use other techniques, they just make my eyes swim.