copy a table - 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

SQL Server 2000 Forums

Transact-SQL (2000)

copy a table

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-09-22 : 11:42:37

hello,
what is the quickest way to make a copy of a table ?

I want to copy a complete table, data and structure. then run a script against the original table, if it fails delete the original table and rename the copied table to the original table.

is this a good idea ?

I am using SQL2000.

thank you,
Jam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 12:00:18

well if it's on the same server you could do

select *
into #temp
from MyTable

and then work with that...
this does not copy keys and such. just data and schema

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-09-22 : 13:16:49

I tend to do

SELECT *
INTO TEMP_OriginalTableName_YYMMDD
FROM OriginalTableName

This will create a table of the same structure as the original, and put the data in it. It will not create PKs [I don't think so anyway], indexes, FKs, etc.

All the crud I accumulate in this was is alphabetically listed under TEMP_xxx, and has the date when the job was done, so its not much hassle to clean up later - by comparison a "Random Naming Convention" has the downside of being less sure whether a table is definitely redundant or not!

To put the data back you could do

TRUNCATE TABLE OriginalTableName
GO
INSERT INTO OriginalTableName
SELECT *
FROM TEMP_OriginalTableName_YYMMDD

Note that the TRUNCATE will fail if you have FKs etc. on the table, in which case a somewhat more complicated UPDATE statement may be required

Kristen

ramakanth_gupta
Starting Member

5 Posts

Posted - 2004-09-23 : 09:49:53

hi,
u have a pearl script(ActiveState ActivePerl 5.6) to generate insert sql sripts from microsoft excel sheet.

all u need to do is go SQL enterprise manager and open the data of table and copy them into XL sheet and run the pearl script u will get the insert scripts

and create table script can be directly obtained when u right click on the table name

Kristen
Test

22859 Posts

Posted - 2004-09-23 : 10:36:52

Sounds like hard work to me!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources