copy table question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

copy table question

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-15 : 13:32:13

Hello,

Is there a way to copy a table into the same database, but append Temp_ to the new table with the original table name. So, the new table would be named:
Temp_OriginalTableName

I'd like to place the code in a stored procedure that can be run automatically and copy whatever table I need using a variable for the table that is to be copied.

Any help on how to do this would be greatly appreciated.

Thank you.
Regards,
-D-

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 13:41:16

with or without data?

SELECT * INTO Temp_OriginalTableName FROM OriginalTableName WHERE 1=0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-15 : 13:54:12

Hi Brett,

I want to copy the data as well and the primary key.

I was trying to understand the WHERE clause. What is that doing?

Thanks for your help. I appreciate it.

Regards,
-D-

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 14:28:02

Well just get rid of the WHERE...it's a predicate that's meant to be false, so no data is copied.

Just take it out.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-15 : 14:40:05

Thanks Brett,

Okay, that makes sense.

If I may ask one more follow-up question. Is there a way to make the new table created a variable with the appended Temp_, so that I can pass in different table names as needed?

Thanks again!
Regards,
-D-

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-15 : 17:20:03

I was able to copy the table, but the constraints and identity were not copied. Is it not possible to copy the contraints and the identity for the table as well?

SELECT * INTO Temp_OriginalTableName FROM OriginalTableName

Thanks,
-D-

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 01:50:17

To have table with constraints, generate the script of the original table and rename tablename and run it. Then just write this insert query

Insert into newtable(column_list)
Select column_list from originalTable

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 09:29:51

quote:
Originally posted by -Dman100-

I was able to copy the table, but the constraints and identity were not copied. Is it not possible to copy the contraints and the identity for the table as well?

SELECT * INTO Temp_OriginalTableName FROM OriginalTableName

Thanks,
-D-

Congrats on 100!

You want dynamic sql...but as for the constraints...look at nr's profile and his link to see how to generate the constraints...but it ain't that simple.

BUT! I would like to stop you at this point and ask what you're doing, because it doesn't seem like a very sound design.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-16 : 12:57:02

Thanks for the help guys.

We are trying to automate a process that can copy a table, pass the key (identity) and append the name of the table with "temp_".

We can do this manually using the DTS wizard, which copies the table, data and we just append "temp_" to the table name using the wizard.

Once the DTS wizard finishes, we can go in and set the primary key.

That works...but, it is a manual process.

If possible, we'd like to automate that process.

The reason we want to copy the table is to work with the data because we don't want to touch the original table data.

We are working with a very large and complex data warehouse, so in a nutshell, we just want to automate some processes to work with data as needed.

We thought there might be an easy way to do this.

Thanks again for your help.
Regards,
-D-

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 13:35:48

I'd like to know what manipulation you plan on doing and if this is for reporting, or building new dimensions, or modifing data so that some updates can be applied to the existing warehouse.

And in all of this, I wonder why you need the contraints or indexes because it sounds like you be performing scans anyway.

Got a sample of what you are going to do.

Here's a sproc that does what you want (except for the constraints)...I like to timestamp things myself



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

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 13:51:23

Here's the other half.

http://www.mindsdoor.net/DMO/DMOScripting.html

It's Nigels script to do what you want...there's more at his site as well...there's one there that gets all the columns for a primary key...

Why you need these things for what you're doing I can't envision.

I don't see how you be able to perform repeatable operatons on different tables, so it seems to be inherently manual anyway.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources