Insert into table using @varaibles

[sdempsey]

I am wondering if someone could help me with the following INSERT statement.

declare @sqltxt as varchar(200)

select @sqltxt = 'INSERT INTO'+ @lookup_table+'('+[Description]+','+

+')values('+@description+','+@code+')'
		
print @sqltxtExec  sp_executesql @sqltxt

Am I right in the fact that all non dynamic bits need to be in ''.

Thanks in advance

 

[gmmastros]

When setting a single value, you should use set, not select. Select should be used when you are working with multiple records.

I assume that each lookup table you are working with has a Description field and a Code field, so you don't need to worry about that part.

set @sqltxt = 'INSERT INTO ' + @lookup_table + '([Description],

) Values('+@description+','+@code+')'




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sdempsey]

Thanks for the response.

All the lookup tables do contain the same fields Description and Code.

The code above complies however sqltxt is blank and nothing happens when I run it.

 

[gmmastros]

I assume you are running this through a stored procedure. I urge you to show the entire stored procedure (or at least the relevant parts).

@Descirption and @Code are variables. If either variable is NULL, then @sqltxt will be NULL because when you add a string to null, the result is null. I expect your stored procedure to look something like this.

Create Procedure InsertLookupTable
  @Lookup_Table VarChar(255),
  @Description VarChar(100),
  @Code VarChar(100)
As

Declare @sqltxt VarChar(1000)

set @sqltxt = 'INSERT INTO ' + @lookup_table + '([Description],[Code]) Values('''+@description+''','''+@code+''')'

sp_executesql @sqltxt

It also looks like I missed a couple of apostrophes. Assuming, of course that the description and code fields are both varchar fields.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom