Copy tables - 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

SQL Server Development (2000)

Copy tables

Author

Topic

kiran
Starting Member

30 Posts

Posted - 2003-06-02 : 18:58:59

What is the command to create a new table using existing table? I want to copy only structure.

Eg: I have a table called Employees. I want to create a new table called Employees_temp with the same structure(columns/datatypes) of
Employees.

Thanks in advance....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 19:01:34

SELECT *
INTO Employees_temp
FROM Employees

Tara

Bambola
Posting Yak Master

103 Posts

Posted - 2003-06-03 : 03:18:25

SELECT *
INTO Employees_temp
FROM Employees
WHERE 1 = 0

Bambola.

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-03 : 03:47:14

The select * into method wont copy the indexes though, so if you want indexes, constraints, etc. you'll need to go into enterprise manager or similar and script the table using the relevant options.

Right click on a database in Enterprise manager and choose All tasks - generate sql scripts to do this.

-------
Moo.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-03 : 12:15:21

quote:

The select * into method wont copy the indexes though, so if you want indexes, constraints, etc. you'll need to go into enterprise manager or similar and script the table using the relevant options.

Right click on a database in Enterprise manager and choose All tasks - generate sql scripts to do this.

-------
Moo.

Kiran only wants to copy the structure.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 12:18:53

Why not just script it...probably wants to do a copy and paste (structure only) like in Access...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 12:21:25

Hey no kidding...I did a copy on a table in Northwind...but it wouldn't let me paste...so I went to QA and hit paste...and it pasted the table DDL (no contraints, ect)

I was amazed.

(but then that's a regular occurance)

Brett

8-)

Shastryv
Posting Yak Master

145 Posts

Posted - 2003-06-03 : 12:21:59

Yes he just wants only the structure, no data.
Co to Enterprise manager, select the table, Copy and Paste it in the QA, change the table name and change the rest of the names if any and execute

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-03 : 12:26:27

I prefer using Query Analyzer for my work. So if no data is needed, then you can grab the DDL from the Object Browser in Query Analyzer. Just navigate to your object, then right click, then click Script Object to New Window As...Create, or click Script Object to Clipboard As...Create, then paste it over in a window.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 13:34:35

HOLY SH_T...

Thanks Tara...NEVER used it...

[homer]dooooh[/homer]

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-03 : 13:43:42

I installed the SQL 2k Client tools even before I had any servers using SQL 2k just so that I could use the Object Browser for SQL 7.0.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources