Post reply

insert statement with variable for column name

  • May 9, 2012 at 8:57 am

    #255623

    Is it possible to do something like this? I'm getting an invalid column name on the column variables. I have a tbl with a lot of columns and am creating insert statements like this. The @FieldVariable1 and @FieldVariable2 variable values are a concatenated string. :crazy:

    INSERT INTO myTable (@FieldVariable1, @FieldVariable2)

    VALUES (@FieldValue1, @FieldValue2)

  • May 9, 2012 at 9:02 am

    #1485122

    You have to explicitly specify the column names i.e. you can't use a variable. Otherwise you don't specify any column names and you specify values for all columns. The final option is dynamic sql - where you build you're insert statement as a string, and then execute it.

    I confess I'm not clear why you want to do this...but I guess you have your reasons!

  • May 9, 2012 at 9:09 am

    #1485127

    you could do this by declaring your statement as a variable....

    declare @sqlcmd varchar(max)

    set @sqlcmd = 'INSERT INTO myTable ('+@FieldVariable1+','+@FieldVariable2+')

    VALUES ('+@FieldValue1+','+@FieldValue2+')'

    exec (@sqlcmd)

    or somethinglike that

  • May 9, 2012 at 9:12 am

    #1485129

    And before you use consider dynamic SQL, please read up on SQL Injection and make sure that you are fully familiar with what it is and how to defend against it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 9, 2012 at 6:03 pm

    #1485404

    Okay thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply