Search code examples
sql-serversql-server-2005selectcreate-table

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

Solution

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

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