Need to create a new table from existing table

Question

I want to create a new table with existing table, where the table names should to pass from input parameters. I am trying the following code.

DECLARE @oldTableName nvarchar(50)
DECLARE @newStagingTableName nvarchar(50)
SET @oldTableName='OldTableName'
SET @newStagingTableName ='NewTableName';
SELECT * INTO @newStagingTableName FROM @oldTableName WHERE 1 = 0;

The SQL server is giving error while parsing this query.

`@newStagingTableNameFROM` is this a typo or have you not added a space? — Kami, Feb 10 '15 at 12:11
Incorrect syntex near '@newStagingTableNameFROM', Expecting '.', ID, or QUATED _ID. — Diboliya, Feb 10 '15 at 12:12
possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) — Kami, Feb 10 '15 at 12:14
The way this is posted leads me to suspect your code is vulnerable to sql injection. All the answers posted so far are vulnerable. You need to wrap the object names with QUOTENAME. It is not perfect but is very simple and helps prevent sql injection quite easily. — Sean Lange, Feb 10 '15 at 14:18

score 2 · Accepted Answer · answered Feb 10 '15 at 12:23

2

Could you please try below dynamic SQL query?

DECLARE @oldTableName nvarchar(50)
DECLARE @newStagingTableName nvarchar(50)

SET @oldTableName='OldTableName'
SET @newStagingTableName ='NewTableName'

DECLARE @sqlquery nvarchar(100) = 'SELECT * INTO ' + @newStagingTableName + ' FROM ' + @oldTableName
exec(@sqlquery)

answered Feb 10 '15 at 12:23

Ashish Sapkale

540
2
13

Wrapping the two table variables with QUOTENAME would go a long way towards preventing sql injection. – Sean Lange Feb 10 '15 at 14:19

Symeon Breen · Answer 2 · 2015-02-10T12:21:18.127

0

On the line

SELECT * INTO @newStagingTableNameFROM @oldTableName WHERE 1 = 0;

you do not have a space between @newStagingTableName and FROM

also check does the table NewTableName exist ? and if so you cannot just access it directly via a parameter - you would need to use dynamic SQL - perhaps this can help

edited Feb 10 '15 at 12:21

answered Feb 10 '15 at 12:12

Symeon Breen

1,531
11
25

score 0 · Answer 3 · answered Feb 10 '15 at 12:28

Try with this

DECLARE @oldTableName NVARCHAR(50)
DECLARE @newStagingTableName NVARCHAR(50),
        @sql                 NVARCHAR(100)=''

SET @oldTableName=''
SET @newStagingTableName ='';
SET @sql='select * INTO ' + @newStagingTableName
         + ' FROM ' + @oldTableName + ' WHERE  1 = 0;'

EXEC sp_executesql
  @sql

score 0 · Answer 4 · answered Feb 10 '15 at 12:35

this should work . . .

DECLARE @oldTableName nvarchar(50)
DECLARE @newStagingTableName nvarchar(50)
declare @sql nvarchar(max);
SET @oldTableName='oldTableName'
SET @newStagingTableName ='newStagingTableName ';

SET @sql='SELECT * INTO ' +  @newStagingTableName + ' FROM ' + @oldTableName + ' WHERE 1 = 0;' 

exec sp_executesql @sql

EDIT: Sorry I didnt see that other guys answered it

Need to create a new table from existing table

4 Answers4