create table from another table in different database in sql server 2005
I have a 开发者_C百科database "temp" with table "A". I created new database "temp2". I want to copy table "A" from "temp" to a new table in "temp2" . I tried this statement but it says I have incorrect syntax, here is the statement:
CREATE TABLE B IN 'temp2'
AS (SELECT * FROM A IN 'temp');
Here is the error:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'IN'. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'IN'.
Anyone knows whats the problem?
Thanks in advance,
Greg
I've not seen that syntax before. This is what I normally use.
SELECT *
INTO temp2.dbo.B
FROM temp.dbo.A
You need to qualify enough of the table name for SQL Server to be able to identify the correct table.
The name structure is <server name>.<database name>.<schema>.<table>
. As both tables live on the same server you can dispense with that, but still need the rest:
SELECT *
INTO temp2.dbo.B
FROM temp.dbo.A
If you want to create a new table in another DB from the current DB, run the query.
CREATE TABLE `destination_database_name`.table_dummy AS (
SELECT * FROM currentDB.table
)
If you don,t want the data and only want the shcema of table without data then u can use this approach also...
SELECT * INTO temp2.dbo.b
FROM temp.dbo.a where 1=0
Query should be:
SELECT * INTO temp2.dbo.b
FROM temp.dbo.a
If you don't want the data you can do:
SELECT TOP 0 * INTO temp2.dbo.b
FROM temp.dbo.a
The easiest way is by right click on table A
from database temp
, then click Script Table as
=> CREATE to
=> New Query Editor Window
. This will create the script.
Then, change following 2 lines. and run it for new database.
USE [temp2]
....
CREATE TABLE [dbo].[B]
.....
Note that SELECT INTO wont copy the indexes. If you want them too, you can generate script from source;run in the target db and do insert into
insert into temp2.dbo.b (columns) select columns from temp.dbo.a
精彩评论