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 - 2005-11-29 : 23:59:56
|
| Chris writes "How do I create a copy of a table (not temporary).In Oracle I do .Create table newtable as select * from oldtableThis command copies data and column data types.I tried entering SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtablein the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns." |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-11-30 : 00:22:43
|
| It seems to be ok.you are trying to copy oldtable in another database db2 as newtable. check the table in db2 database. otherwise u can use dts to transfer table to another db. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-30 : 02:52:04
|
| Create the script of oldtable ;rename table name and run it in db2ThenInsert into db2.dbo.newtable Select * from db1.dbo.oldtableMadhivananFailing to plan is Planning to fail |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-30 : 03:10:38
|
| I think you can also use SELECT * FROM db1.dbo.oldtable INTO db2.dbo.newtablesteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-30 : 03:36:54
|
quote:
Originally posted by elwoos
I think you can also use SELECT * FROM db1.dbo.oldtable INTO db2.dbo.newtablesteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true!
Not valid in SQL ServerMadhivananFailing to plan is Planning to fail |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-30 : 03:50:04
|
quote:
Not valid in SQL Server
Fair point as I get quote:
Incorrect syntax near the keyword 'INTO'.
but BOL does imply otherwise quote:
The database in which the object resides may not always be the current database when the SELECT statement is executed. To ensure that the proper object is always used, regardless of the current database setting, qualify the object name with the database and owner: SELECT *FROM Northwind.dbo.Shippers
steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-30 : 04:05:16
|
| Well.Did you mean this?select '10' as Number into #tSelect number from #tDrop table #tMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
creating tables