Create table as existing table

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 Administration (2000)

Create table as existing table

Author

Topic

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-12 : 03:25:32

Hello,

Does anyone know if you can create a table using the stucture of an existing table.
Something like "CREATE TABLE Newtable_A FROM Existingtable_B "
I don't use select into/bulk copy, but full recovery instead and sql sever 7.0.

Thanks in advance.

Best regards,

Harry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 03:46:00

[code]select * into Newtable_A from Existingtable_B[/code]
this will create the structure but without constraints etc

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-12 : 04:10:21

Thanks,

But this is just NOT want i want.
selec into uses bulk copy, and my database is in full recovery modus.
I am looking for another statement, if exists....

Harry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 04:14:48

You can also use Enterprise Manager to script out the table structure change the table name and run it

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-12 : 04:51:40

mmm,
I was afraid you would suggest this.
Still hope for an existing sql statement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 07:14:53

That's the easier way. If you want the harder way, create your script from INFORMATION_SCHEMA.COLUMNS

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-12 : 07:56:07

ok, that i havent thought about.

Thanks

JeffK95z
Starting Member

19 Posts

Posted - 2006-07-12 : 18:14:56

this may be completely too simple for what your looking for, but hopefully i'm understanding correctly.

All you want is the empty table structure?

Our database tables are typically stand alone, so no keys or such.

I do this alot to get just an empty structure :)

edit: i'm guessing you didn't like the select into above cause its logged as your in full recovery mode? At least with below nothing ever gets moved, so nothing really to log!

select
*
into
test_copy
from
test
where
1 = 2

seeing how 1 will never equal 2, you just get an empty table! Somehow I think this is too basic for what your looking for though :)

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-13 : 14:41:47

Yeff,

This is what was already suggested.
But "select into" you cannot be done for a full recovery model database.
That is why i am looking for something else
Harry

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-13 : 14:46:46

Yeff,

Sorry i did not read your reply fully. You also mentioned the full recovery issue.

If this statement will work, i'm happy.
I will try an see...

Harry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 14:59:30

I don't understand why you think select into on a full recovery model database won't work. Could you explain? Do you get an error?

Tara Kizer
aka tduggan

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2006-07-14 : 13:15:35

Tara,

When i do use "select into " statement, and make a backup of the transaction log, we receive an error: "a non-logged operation has been performed".
I must take a full backup of the database, to clear this.
This is in a 7.0 environment.
But today i tested with select into .. where 1=2 and now the translog can be backup without any error.
It seamns that the "where 1=2" is a non-logged operation, and did the trick.

Best regards,

Harry

Hart
Starting Member

1 Post

Posted - 2011-05-23 : 13:48:04

I found this page at the top of my Google search results, and it doesn't contain a correct answer to the question.

So I kept researching, found the answer, and came back to post it.

In order to create a new table based on an existing table without trouble, you'll want something like:

SELECT TOP 0 *
INTO MyNewTableName
FROM MyExistingTable;

This will create a table with zero rows, duplicating the structure of the existing table.

Excluding the TOP 0 will create a duplicate of the existing table with the data, equivalent to CREATE TABLE <name> AS SELECT <...> WITH DATA command from other dialects of SQL.

If you found this answer through Google, please ensure that the top result for your future searches is updated with an accurate answer. You might save me work later!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-23 : 13:49:46

Hart, the answer was provided by JeffK95z already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rahulh
Starting Member

1 Post

Posted - 2013-04-23 : 02:00:10

select top 1 * into newtablename from oldtablename
RAHUL HUNDRAE

Rahul Hundare

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources