CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012-express
Deina Underhill
Deina Underhill

Reputation: 567

Referring to a table by variable

I'm sure this is pretty basic, but how do I set up an INSERT statement using a variable for the table name?

For example, I have a number of input files, all configured identically (Input1, Input2, Input3, ...) all going to an INSERT or MERGE statement.

I want to either do a loop, working through all the input files, or call the INSERT statement as a function

INSERT INTO [OutputFile]
SELECT i.*
FROM   [<Input Variable>] i
     LEFT JOIN [OutputFile] OP
         ON CONCAT(i.Field1, i.Field6) = CONCAT(OP.Field1, OP.Field6) 
     WHERE OP.Field1 IS NULL 

PRINT 'Number of rows added is ' + CAST(@@ROWCOUNT as char(6));

I'll actually be using MERGE statements, but I assume the process will be the same.

Upvotes: 0

Views: 99

Answers (3)

pyrospade
pyrospade

Reputation: 8078

You can use dynamic SQL. The QUOTENAME() function will use and escape square brackets to help prevent SQL injection.

declare @table sysname = 'MyTable';

declare @sql nvarchar(max) = '
    INSERT INTO [OutputFile]
    SELECT i.*
    FROM {{table}} i
    LEFT JOIN [OutputFile] OP
        ON (CONCAT(i.Field1, i.Field6)
            = CONCAT(OP.Field1, OP.Field6))
    WHERE OP.Field1 IS NULL
    ';

set @sql = REPLACE(@sql, '{{table}}', QUOTENAME(@table));

exec(@sql);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Why do a loop? Just use union all:

insert into OutputFile
    select i.*
    from ((select * from input1) union all
          (select * from input2) union all
          . . .
          (select * from inputn)
         ) i left outer join
         OutputFile op
         on CONCAT(i.Field1, i.Field6) = CONCAT(OP.Field1, OP.Field6) 
    WHERE OP.Field1 IS NULL

If there are lot of tables, you can put them in a column in Excel and use Excel formulas to create the query.

Upvotes: 0

Oded
Oded

Reputation: 499002

how do I set up an INSERT statement using a variable for the table name?

You don't, not directly with SQL. Table names and column names cannot be variables.

You can achieve this by using dynamic SQL, but you have to be careful not to introduce SQL Injection.

The Curse and Blessings of Dynamic SQL is a fantastic in depth article discussing dynamic SQL.

Upvotes: 4

Related Questions