[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

variable in an insert statement

I am trying to insert a variable into an insert statement that contains a list of rows. When I attempt to put the variable in an insert statement it doesn't show up on the intellisence list, and if I just type it in I get red squiggly line. It does however show and works in the select statement. I suspect it has something to do with the parens in the insert statement. Can this be done?

Declare @cols varchar(max)

Set @cols = "the routine that builds the column list"

insert into table1(field1,field2, @cols)<-- this on doesn't work
select field1, field2, @cols <-- this one works
From Table2
0
rcttrmn
Asked:
rcttrmn
  • 2
  • 2
  • 2
1 Solution
 
käµfm³d 👽Commented:
That's because your're trying to use the variable in the column list and not the value list. Include the "values" keyword.
insert into table1 values (field1,field2, @cols)

Open in new window

0
 
käµfm³d 👽Commented:
P.S.

The syntax above assumes your table has three columns and that you are listing the parameters in the order the columns appear in your table. If your table has more than 3 columns, or your columns are in a different order, then you will have to specify the column list in addition to the values.
0
 
dqmqCommented:
The second one passes syntax check, but it doesn't do what you want. You need to use dynamic SQL:

Declare @cols varchar(max)
declare @sql varchar(max)

Set @cols = "the routine that builds the column list"

set @sql = 'insert into table1(field1,field2,' +  @cols + ') '
set @sql = @sql + 'select field1, field2,' + @cols + ' from table2'


EXEC (@sql)
 
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
rcttrmnAuthor Commented:
I need it in the column list and the Values come from the select statement. Basically it is a really long list of columns and I don't want to clutter the query. If I build a dynamic query string I can just add '+ @cols +' and it works with "exec" but I can't put it in a while loop.
0
 
rcttrmnAuthor Commented:
The dynamic sql does work. I just need to figure out how to pass variables into a loop using exec sp_executesql.

Thanks for the help.
0
 
dqmqCommented:
The first doesn't work because you cannot put a variable (containing field names) in the field list of an insert statement.  

The second attempts to insert the contents of the variable (list of field names) into one column of the field list.  That is not what you want.

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now