Copy a table?

Question

Post reply

Copy a table?

Julie Hargraves

SSCarpal Tunnel

Points: 4457
More actions
February 15, 2007 at 9:10 am

#194246

This is such a newbie question, and I feel like I should know this already, but it's not something I have to do everyday or ever for that matter.
I have a business analyst telling me to make some copies of tables for her. She wants exact copies of the data and column structure and everything. Also, these tables have to be in the same database. I was able to make a copy of the table structure by generating SQL scripts and altering the name slightly as I do that pretty often, but I don't usually have a need to copy the data as well. I used BCP when I was in college for a few projects, but I've been away from it for so long. I looked up the documentation in BOL but I'm having a hard time finding what I want as I don't want to copy to a file, I just want to copy the data exactly from one table to another. Can someone help me or do I have to copy to data files as a middle man?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 75905 More actions · Answer 1

The quick and dirty method is

SELECT * INTO NewTable FROM sourcetable

However, if the sourcetable is large then this may impact on your servers performance.

LinkedIn Profile

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 2

Thanks so much! Had no idea you could insert rows into a table like that. This is a development server, so I'm not too worried about the performance. I'll just do it after business hours when no one's using the server.

David.Poole SSC Guru Points: 75905 More actions · Answer 3

If you want to speed up the insert and you don't have identity/autonumber fields you can do

SELECT * INTO dbo.NewTable FROM dbo.SourceTable WHERE 1=0

Followed by

INSERT INTO dbo.NewTable SELECT * FROM dbo.SourceTable

This is quicker because the two stage process is inserting data into a known structure where as the SELECT INTO runs the whole select before creating the structure.

LinkedIn Profile

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 4

I knew I was making it more difficult than it had to be by generating SQL scripts and considering using BCP. Thanks very much for your help. Sometimes the simplest way of doing things just doesn't jump out at me, and I always forget you can use SELECTs with INSERTs.

Brian Munier SSCrazy Points: 2875 More actions · Answer 5

The insert statement above is excellent since you already had copied the structure.

One other suggestions is:

Use DTS to "export" the data. You can tell it to copy the structure, as well as permissions, keys, etc. And you can tell it to copy tables renaming the structure. Note: If you do copy to a different database, you actually have more options to change.

Brian

vadba SSChampion Points: 11133 More actions · Answer 6

Be aware that the SELECT * INTO method doesn't copy indexes or triggers. If you need an EXACT copy, including those objects, the you can fully script the object as you were originally doing, then copy the data using INSERT [copy] SELECT * FROM [source]