CODEWITHSUNDEEP
sqlsql-server-2008
user1125803
user1125803

Reputation: 27

SQL Server stored procedures to copy tables

I have to move a table into other table using a stored procedure by passing the table names as parameters.

Syntax is:

alter procedure [dbo].[moving]
(
    @to_table varchar(50),
    @from_table varchar(50)
)
as
begin
    EXEC('Select * into '+@to_table+'from '+@from_table)
end

while executing by.

exec moving newtable,hello

It is giving an error:

Incorrect syntax near 'hello'

pls anyone give solution for this

Upvotes: 1

Views: 3757

Answers (4)

Oleg Dok
Oleg Dok

Reputation: 21766

SELECT ... INTO needs to create table, if table exists use INSERT INTO ... SELECT ..FROM

AND

in your case you need to run SP in such a way:

EXEC dbo.moving 'table1', 'table2'

BUT 

EXEC('Select * into '+@to_table+' from '+@from_table)

will not work, you need to rewrite it with variable:

declare @sql nvarchar(max)
SET @sql = N'Select * into ['+@to_table+N'] from ['+@from_table+N']'
EXEC(@sql)

BUT

you also need to worry of sql injections and complex table names AT LEAST, so - for complex table names I already framed your tables with square braces, and you need to do something to prevent sql injections.

And once more - SELECT...INTO works only if you creating new table with name from @to_table parameter

Upvotes: 1

John Woo
John Woo

Reputation: 263723

add an extra space after single quote and FROM

EXEC('Select * into ' + @to_table + ' from ' + @from_table)

Upvotes: 0

xbrady
xbrady

Reputation: 1703

Try:

exec moving 'newtable','hello'

It also looks like you are going to need to fix your SP. You will need a space before from:

EXEC('Select * into '+@to_table+' from '+@from_table)

Upvotes: 3

KV Prajapati
KV Prajapati

Reputation: 94645

Read EXECUTE syntax and try,

EXEC moving 'newtable','hello'

Upvotes: 1

Related Questions