SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Create table as existing table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/12/2006 :  03:25:32  Show Profile  Reply with Quote
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)

Singapore
17437 Posts

Posted - 07/12/2006 :  03:46:00  Show Profile  Reply with Quote
select * into Newtable_A from Existingtable_B

this will create the structure but without constraints etc


KH

Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/12/2006 :  04:10:21  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 07/12/2006 :  04:14:48  Show Profile  Reply with Quote
You can also use Enterprise Manager to script out the table structure change the table name and run it


KH

Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/12/2006 :  04:51:40  Show Profile  Reply with Quote
mmm,
I was afraid you would suggest this.
Still hope for an existing sql statement


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 07/12/2006 :  07:14:53  Show Profile  Reply with Quote
That's the easier way. If you want the harder way, create your script from INFORMATION_SCHEMA.COLUMNS


KH

Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/12/2006 :  07:56:07  Show Profile  Reply with Quote
ok, that i havent thought about.

Thanks
Go to Top of Page

JeffK95z
Starting Member

Canada
19 Posts

Posted - 07/12/2006 :  18:14:56  Show Profile  Reply with Quote
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 :)

Edited by - JeffK95z on 07/12/2006 18:19:08
Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/13/2006 :  14:41:47  Show Profile  Reply with Quote
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
Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/13/2006 :  14:46:46  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 07/13/2006 :  14:59:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

hvdtol
Yak Posting Veteran

Netherlands
50 Posts

Posted - 07/14/2006 :  13:15:35  Show Profile  Reply with Quote
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
Go to Top of Page

Hart
Starting Member

1 Posts

Posted - 05/23/2011 :  13:48:04  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/23/2011 :  13:49:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 05/23/2011 13:50:38
Go to Top of Page

rahulh
Starting Member

India
1 Posts

Posted - 04/23/2013 :  02:00:10  Show Profile  Reply with Quote
select top 1 * into newtablename from oldtablename
RAHUL HUNDRAE

Rahul Hundare
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000