views:

35

answers:

4

Hi,

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

+4  A: 

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

SELECT * 
INTO temp2.dbo.B
FROM temp.dbo.A
Martin Smith
Thanks, that did it :D
Greg
+1  A: 

Query should be:

SELECT * INTO temp2.dbo.b
FROM temp.dbo.a
egrunin
I see my and @Martin's answers evolved into each other :)
egrunin
Just as well as I think there is only one right syntax for this!
Martin Smith
+1  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
Oded
Thanks! That did the trick :)
Greg
A: 

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

Madhivanan