Dynamically duplicate/create table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Dynamically duplicate/create table

Author

Topic

lesleewilcox
Starting Member

3 Posts

Posted - 2013-10-28 : 14:07:32

I need to be able to create a duplicate table on the fly. I have created a procedure that has two parms; the new table and the table to copy.

CREATE PROCEDURE [dbo].[CopyTable]
-- Add the parameters for the stored procedure here
@p1 varchar(100) = 0, -- New Table name
@p2 varchar(100) = 0 -- Copy from Table
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tempstring as Varchar (8000)

set @tempstring = 'IF OBJECT_ID(''' + @p1 + ''',''U'') is not null'
execute sp_sqlexec @tempstring
Begin
set @tempstring = 'drop table ' + @p1
execute sp_sqlexec @tempstring
end

set @tempstring = 'SELECT * into ' + @p1 + ' from ' + @p2 + ' where 1=0'
execute sp_sqlexec @tempstring

END

When I step through it in debug and manually execute the statements the code creates it works fine. When I call it I get errors.

EXEC PUBS.DBO.CopyTable 'NewTable','OldTable'

The errors are as follows:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'null'.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'NewTable', because it does not exist or you do not have permission.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OldTable'.

The OldTable does exist in the database I am running the procedure from.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 14:26:58

You should combine the testing for the existence of the table and the drop command into one dynamic sql string

SET @tempstring = 'IF object_id('''+@p1+''',''U'') IS  NULL DROP TABLE ' +@p1;
execute sp_sqlexec @tempstring;

That aside, when you have to drop and recreate table on the fly, and when you have to do it using dynamic SQL, more often than not, that suggests some room for design improvements. Consider if you really need to do this. There may be better and simpler ways of accomplishing it.

If you describe the business problem you are trying to solve, some of the people on this forum might be able to offer easier ways of doing it.

lesleewilcox
Starting Member

3 Posts

Posted - 2013-10-28 : 14:30:25

I tried that first and did not get an error; however it did NOT drop the table.

lesleewilcox
Starting Member

3 Posts

Posted - 2013-10-28 : 14:33:39

You are correct with the design. I can do it without the dynamic sql for this particular issue. I was thinking I could use it for other situations as well which is why I thought creating a function would be worth it. I can see that it really wouldn't necessarily make it easier. Thanks for the proper perspective.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 14:47:18

Ooops! I forgot a "NOT". See in red below:

SET @tempstring = 'IF object_id('''+@p1+''',''U'') IS NOT NULL DROP TABLE ' +@p1;
execute sp_sqlexec @tempstring;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources