Hello all.

I'm inserting data from multiple databases on one server into a single database and I'd like to be able to generate the tables names I'm inserting to and selecting from in variables. I have 4 separate environments that these scripts need to run in and I'd like to avoid having to alter them every time they move to another environment.

Here's a simple example of what I'm trying to do:

DECLARE @Source varchar(50),
        @Target varchar(50)

select @Target = '[' + table_catalog + ']' + '.' + '[' + table_schema + ']' + '.' + 'Charge'
                from Information_Schema.tables where table_name = 'Charge'


select @Source = '[' + table_catalog + ']' + '.' + '[' + table_schema + ']' + '.' + 'Charge'
                from [asapsql].Information_Schema.tables where table_name = 'Charge'

INSERT INTO [@Target]
        ( [ASAP_Charge_ID]
        , [Charge_Description]
        , [Update_User_ID]
        , [Update_DateTime])
SELECT [Charge_ID]
        , [Charge_Description]
        , [Update_User_ID]
        , [Update_DateTime]
FROM [@Source]

Using the variables in the insert statement yields and "Invalid object name '@Target'." error.

Any suggestions for generating the table names for use in the insert statement?

Thanks!!

Hi,

You got to construct those insert statements into separate variables and use EXEC() function to execute that, as these are dynamic sql statements. Something that looks like this Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

It does help - I was hoping there was another method, but I guess it will have to do.

Thanks!