x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How can I automate the copying of a table?

As much as I try to disuade people from building processes that might come back to haunt them later, this one always seems to come up.  So I gave in, I figure it might be useful from an admin perspective at some point, so here it is.

Use it at you're own risk, and don't go swimming at least 1 hour after use.

CREATE PROC usp_CopyTable
   @TABLE_CATALOG  sysname
 , @TABLE_SCHEMA   sysname
 , @TABLE_NAME     sysname
 , @TMP_TABLE_NAME sysname OUT
AS
  SET NOCOUNT ON
  BEGIN
 DECLARE @sql varchar(8000), @GetDate varchar(25)
 IF EXISTS (SELECT *
       FROM INFORMATION_SCHEMA.Tables
      WHERE TABLE_CATALOG = @TABLE_CATALOG
        AND TABLE_SCHEMA  = @TABLE_SCHEMA
        AND TABLE_NAME    = @TABLE_NAME)
   BEGIN
  SET @GetDate = REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),' ','_'),':','_')
  SET @TMP_TABLE_NAME = '['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+'TMP_'+@TABLE_NAME+'_'+@GetDate+']'
  SET @sql = 'SELECT * INTO
'+@TMP_TABLE_NAME+' FROM '
   +'['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+@TABLE_NAME+']'
  EXEC(@sql)
   END
 ELSE
   PRINT 'Table ' + '['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+@TABLE_NAME + '] Does not Exists'
 
   RETURN
  SET NOCOUNT OFF
  END
GO

DECLARE @TMP_TABLE_NAME sysname, @sql varchar(8000)
EXEC usp_CopyTable 'Northwind','dbo','Order Details',@TMP_TABLE_NAME OUT
SELECT @TMP_TABLE_NAME
SELECT @sql = 'SELECT COUNT(*) FROM ' + @TMP_TABLE_NAME
EXEC(@sql)
SELECT @sql = 'DROP TABLE ' + @TMP_TABLE_NAME
GO

DROP PROC usp_CopyTable
GO

Print | posted on Tuesday, August 16, 2005 1:40 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How can I automate the copying of a table?

Can you help me relize what can automate in this code?
9/5/2005 2:31 AM | Nguyen Thi Linh
Gravatar

# ummmm...cut & paste?

All you would have to do is cut and paste the code into Query Analuzer and it should "automate" already.

That's all you'd have to do.
9/7/2005 8:50 AM | Brett
Gravatar

# all posters

Welcome to the World's Largest Poster, Print & Frame Store
9/28/2005 11:57 AM | antonio hicks
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET