从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:

我有一个数据库“temp”与表“A”。我创建了新的数据库“temp2”。我想将表“A”从“temp”复制到“temp2”中的新表。我试过这个语句,但它说我的语法不正确,这里是声明:

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'.

消息156,级别15,状态1,行2关键字“IN”附近的语法不正确。消息156,级别15,状态1,行3关键字“IN”附近的语法不正确。

Anyone knows whats the problem?

谁知道这个问题是什么?

Thanks in advance,

提前致谢,

Greg

8 个解决方案

#1

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

我之前没见过那种语法。这就是我通常使用的。

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

#2

You need to qualify enough of the table name for SQL Server to be able to identify the correct table.

您需要为SQL Server限定足够的表名才能识别正确的表。

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

#3

Query should be:

查询应该是:

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

#4

If you don,t want the data and only want the shcema of table without data then u can use this approach also...

如果你不想要数据并且只想要没有数据的表的shcema那么你也可以使用这种方法......

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

#5

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
)

#6

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

请注意,SELECT INTO不会复制索引。如果你也想要它们,你可以从源代码生成脚本;在目标数据库中运行并插入

insert into temp2.dbo.b (columns) select columns from temp.dbo.a

插入temp2.dbo.b(列)从temp.dbo.a中选择列

#7

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

如果您不想要数据,您可以执行以下操作:

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

#8

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.

最简单的方法是右键单击数据库temp中的表A,然后单击Script Table as => CREATE to => New Query Editor Window。这将创建脚本。

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

然后,改变2行。并为新数据库运行它。

USE [temp2]

....

CREATE TABLE [dbo].[B]

.....

#1