CODEWITHSUNDEEP
sql-serversql-server-2005selectcreate-table
Greg
Greg

Reputation: 163

create table from another table in different database in sql server 2005

I have a 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

Upvotes: 8

Views: 62877

Answers (8)

Muhammad Abbas
Muhammad Abbas

Reputation: 129

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
)

Upvotes: 3

Mohamamd
Mohamamd

Reputation: 11

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]

.....

Upvotes: 1

Rae Lee
Rae Lee

Reputation: 1391

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

Upvotes: 2

Lawrence
Lawrence

Reputation: 11

If you don't want the data you can do:

SELECT TOP 0 * INTO temp2.dbo.b
FROM temp.dbo.a

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 0

Oded
Oded

Reputation: 498904

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

Upvotes: 7

egrunin
egrunin

Reputation: 25053

Query should be:

SELECT * INTO temp2.dbo.b
FROM temp.dbo.a

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452957

I've not seen that syntax before. This is what I normally use.

SELECT * 
INTO temp2.dbo.B
FROM temp.dbo.A

Upvotes: 25

Related Questions