Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Australia
    Posts
    2

    Unanswered: Insert into dynamic field

    I am having trouble trying to insert into a dynamic field.

    This is the code I have at the moment it should hopefully be self explanatory.
    --------------------------------------------------------------

    declare @field as varchar(15)

    select @field = ColumnA from Table1 where ColumnB = @value1

    insert into Table2 (@field) values (@value2)

    --------------------------------------------------------------
    This sets @field to the result of the select statement (ie the value that was in column a when ColumnB = @value1). That much works I know for sure.

    The problem occurs when I try to insert into Table2 based on that information. It doesn't like me specifying the Column to insert into Table2 as @field. I have tried doing all types of conversions and have still not managed to find a solution. I am hoping someone is able to help me.

    Thanks in advance
    Dan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't submit a parameter as a field name. You'll have to cast the entire INSERT statement as dynamic sql:

    declare @sqlString varchar(500)
    set @sqlString = 'insert into Table2 (' + @field + ') values (' + @value2 + ')'
    Exec @sqlString

    You will also need different syntax to create the @sqlString depending on the datatype of @value2 (char vs. numeric vs. date etc...).

    blindman

  3. #3
    Join Date
    Sep 2003
    Location
    Australia
    Posts
    2
    Thanks for the info Blindman.

    I tried your suggestion however I forgot to mention that I am trying to do this within a stored procedure.

    @value2 is a varchar type so I assume that I would still declare @sqlquery as varchar(500) or whatever length is needed.

    When I try the execute statement it says: Could not find stored procedure 'insert into Table2 (ColumnName) values (xyz)'.

    Is there something that I might be missing?

    Any extra help you could provide would be great.

    I have also tried sp_executesql but couldn't quite understand how it worked.

    Thanks in advance

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. Wrong syntax.

    Exec (@sqlString)

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •