Create a table from another table in another database in sql server 2005

Question

I have a database "temp" with table "A". I created a new database "temp2". I want to copy table "A" from "temp" to a new table in "temp2". I tried this statement, but it says that I have the wrong 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 next to the keyword "IN". Msg 156, Level 15, State 1, Line 3 Invalid syntax next to the keyword "IN".

Does anyone know what the problem is?

Thanks in advance,

Greg

Answer 1

. , .

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

Answer 2

SQL Server, .

<server name>.<database name>.<schema>.<table>. , , :

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

Answer 3

:

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

Answer 4

, shcema , ...

SELECT * INTO temp2.dbo.b
FROM temp.dbo.a where 1=0 

Answer 5

, SELECT INTO . , script , db

temp2.dbo.b() temp.dbo.a

Answer 6

, :

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

Answer 7

The easiest way is to right-click on a table Afrom the database temp, then press Script Table as=> CREATE to=> New Query Editor Window. This will create a script.

Then change the following 2 lines. and run it for the new database.

USE [temp2]

....

CREATE TABLE [dbo].[B]

.....

Answer 8

If you want to create a new table in another database from the current database, run the query.

CREATE TABLE `destination_database_name`.table_dummy AS (
SELECT * FROM currentDB.table
)