Referring to a table by variable

Question

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.

score 4 · Accepted Answer · answered Feb 09 '13 at 20:52

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.

score 1 · Answer 2 · answered Feb 10 '13 at 01:49

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);

score 0 · Answer 3 · answered Feb 09 '13 at 20:58

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.

Referring to a table by variable

3 Answers3