--exec sp_clone_table 'TESTDB','emptable','emptable_1'
--drop PROCEDURE sp_clone_table
create PROCEDURE sp_clone_table
@dbName varchar(100), @sourcetable varchar(200), @desttable varchar(200)
as
DECLARE @SQL NVARCHAR(MAX)
BEGIN
IF OBJECT_ID(@dbName +'.dbo.'+ @desttable, N'U') IS NULL
SET @SQL =
('SELECT *
INTO ' + @dbName +'.dbo.'+ @desttable +
' FROM
' + @sourcetable)
ELSE
SET @SQL= ' INSERT ' + @dbName +'.dbo.'+ @desttable + ' SELECT * FROM ' + @sourcetable
EXEC (@SQL)
end
SQL Server SP to Clone a Table
This SP will clone a table using dynamic SQL using INSERT statement. This SP will create destination table as well,if not exist.