0

I have a temporary table with 5 columns. I get four columns from existing tables so make use of insert with select statement, which works fine.

However the column which is not populated I need it to be a string which is basically a parameter value. I am trying to run a simple update statement after the insert and I get an error invalid column.

Some of the code is as follows. At this point the parameters are already populated with respective values.

exec ('CREATE TABLE ' + @temp_table_runID + '(
        [DBName] [nvarchar] (100),
        [RunID] [bigint] ,
        [OrchID] [bigint] ,
        [OrchVersion] [bigint] ,
        [TimeStamp] [bigint] 
      ) ON [PRIMARY]')

--exec('select * from ' + @temp_table_runID)
-- Insert from the primary database
exec(' INSERT INTO ' + @temp_table_runID + '
        (
        [RunID],
        [OrchID] ,
        [OrchVersion] ,
        [TimeStamp]
        ) 
SELECT R.RunID, R.OrchID, R.OrchVersion, R.TimeStamp
FROM ' + @primaryDB + '.dbo.Run R, ' + @primaryDB + '.dbo.SeriesVariables S
WHERE S.SeriesVariables_Value = ''SplitSystemTest2''
AND S.Series_ID = R.RunID order by R.Timestamp ASC')

-- The below statement does not work and get an error invalid column name 'reportingdb_24feb14'. The value of the @primaryDB = reportingdb_24feb14. 

-- update the table with database name
exec('update ' + @temp_table_runID + ' SET DBName = ' + @primaryDB)

Any help is greatly appreciated. Thanks.

Martin
  • 16,093
  • 1
  • 29
  • 48
Abe
  • 1,879
  • 2
  • 24
  • 39

1 Answers1

2

I think this is just a simple problem with your @primaryDB parameter not being surrounded by single-quotes:

exec('update ' + @temp_table_runID + ' SET DBName = ''' + @primaryDB + '''')

You would have been generating the SET statement as:

SET DBName = <param>

When in fact you probably want:

SET DBName = '<param>'
Martin
  • 16,093
  • 1
  • 29
  • 48
  • I am just wondering is there anyway instead of having a separate update statement. I can get the DBName value inserted in the insert statement. Thank you. – Abe Feb 25 '14 at 15:05
  • I simply did select ''' + @primaryDB + '''...blah blah as shown above and it works like a charm. No need of separate update statement. Cheers All. – Abe Feb 25 '14 at 15:10