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 KH |
|
|
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 KH |
|
|
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 KH |
|
|
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_copyfrom testwhere 1 = 2seeing 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 elseHarry |
|
|
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 Kizeraka 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 |
|
rahulh
Starting Member
1 Post |
Posted - 2013-04-23 : 02:00:10
|
select top 1 * into newtablename from oldtablenameRAHUL HUNDRAERahul Hundare |
|
|
|