variable in an insert statement

Posted on 2011-05-05
Last Modified: 2012-05-11
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
Question by:rcttrmn
    LVL 74

    Expert Comment

    by:käµfm³d 👽
    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

    LVL 74

    Expert Comment

    by:käµfm³d 👽

    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.
    LVL 42

    Accepted Solution

    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)

    Author Comment

    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.

    Author Closing Comment

    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.
    LVL 42

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now