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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-30 : 09:26:43
|
| diane writes "How do you create a table from an existing table in SQL Server? Knowing there is a simple sql stmt to do this using an ORACLE DB, I assume there has got to be an easy way to do this using a MS SQL DB.The SQL in ORACLE is:create table <table_name> as (select * from <table_name>)This sql stmt will create a duplicate of the existing table, however it does not work using sql server." |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2001-11-30 : 09:41:18
|
| SELECT * INTO tblNewTable FROM tableSource*************************Just trying to get things done |
 |
|
|
pmisiowiec
Starting Member
1 Post |
Posted - 2002-04-11 : 18:16:53
|
| Does this command also duplicate the indexes and foreign key relationships that exist for the parent table? |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-11 : 20:36:24
|
| No it will not.When you execute "SELECT * INTO tblNewTable FROM tableSource" you will just be copying the data from tableSource into tblNewTable.In ORACLE would that command also copy the foreign keys and indexes?Got SQL? |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-11 : 20:39:44
|
Actually I was just reading in BOL that quote:
You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.
It says it creates and exact table definition. I am going to run a test tonight to see if the foreign keys as indexes will be copied as well.Sorry about the previous post.Got SQL? |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-11 : 20:46:29
|
| I just ran this test and the indexes were not copied over. Apparently it will create the base of the table for you and copy over any data if necessaryGot SQL? |
|
|
|
dsdeming
479 Posts |
Posted - 2002-04-11 : 23:17:54
|
| I know in older versions of SQL Server the SELECT INTO syntax caused a lot of problems. Because both the table creation and population make up an atomic transaction, SELECT * INTO #temptable FROM someothertable would lock up tempdb for the duration of the insert. I'm not sure if this is the case any more, but old habits die hard. I still do SELECT * INTO #temptable FROM someothertable WHERE 1 = 2 and then INSERT INTO #temptable SELECT * FROM someothertable. |
|
|
|
|
|
|
creating tables from existing tables