Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 6th, 2003, 08:52 AM
Authorized User
 
Join Date: Jun 2003
Location: Richmond, VA, USA.
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?
Reply With Quote
  #2 (permalink)  
Old June 6th, 2003, 09:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old June 6th, 2003, 09:25 AM
Registered User
 
Join Date: Jun 2003
Location: Hyderabad, Andhra Pradesh, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to prakashvj
Default

Try this

select * into Table2 from Table1 where 1=2

prakash
Reply With Quote
  #4 (permalink)  
Old June 6th, 2003, 10:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 540
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.

Josh
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:00 PM.


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