Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 6th, 2003, 08:52 AM
Authorized User
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy a Table

How can you copy a table in SQL Server 2000. All I want is to duplicate the structure of a pre-existing table in the same database. I don't care if I don't get the data. It has to be easy to do but I can't find anywhere in the help where they tell you how to do it?
Old June 6th, 2003, 09:03 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

If you don't care about the data, you could generate a script to create the table, preview it, edit it to change the name, copy it to the clipboard, paste it into QA then execute it.

You could also use the proprietary, non standard, SELECT ... INTO construct which will create a new table from a source table and populate it with the data from the source. Add a WHERE clause with a predicate that evalutes to FALSE to create the structure without copying any rows.

Jeff Mason
Custom Apps, Inc.
Old June 6th, 2003, 09:25 AM
Registered User
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to prakashvj

Try this

select * into Table2 from Table1 where 1=2

Old June 6th, 2003, 10:51 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 540
Thanks: 0
Thanked 4 Times in 4 Posts

Thought you might like a graphical way as well. If you have access to Enterprise Manager, you can also do it this way. Open EM and right click on the server that holds the database.

Click on All Tasks, then Import Data. This will bring up the DTS Import/Export wizard. Click Next once.

Choose your Data Source server, then database and click next. Choose the destination server, and the database (should be the same as previous if you are copying to same location). Click Next. Select Copy table(s) and view(s) from the source database and click Next.

In the Table(s) and View(s) list, scroll down until you see the table you want to copy and check it. Once you check it, you will see the destination box right next to it fill. In order to not overwrite the current database, you [u]must</u> change the name of the table in the destination field (I will usually just append a number to the name).

Click Next. Make sure that Run Immediately is checked. Click Next. On the next screen, once you click Finish, the table should copy. It will be an exact copy of the other table with some exceptions. For example, your primary keys will have to be redefined as will your Identitys.

Once you are done, you can select any rows that you don't want and delete them.

Hope this helps.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to copy a record from one table to another. lryckman Access VBA 7 July 29th, 2017 12:49 PM
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
Copy table structure sagarbhargava SQL Server 2000 3 November 16th, 2006 03:16 AM
copy one table at server1 to another table Jane SQL Server 2000 2 January 31st, 2005 11:11 AM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.