如何使用SQL将一个表从database1复制到database2?我的代码结果最终丢失了主键 [英] How to copy one table from database1 to database2 using sql?My codes result finally lost primary key

问题描述

我像这样使用sql，最后我可以从table1复制表结构和数据，但是我发现table2没有主键，而我的table1实际上有一个名为id的主键，每次自动加1. br/>

I use the sql like this,finally I can copy the table struct and data from table1,but I find table2 do not have primary key,and my table1 actually have a primary key called id which automatically adds 1 each time.

select *  into database2.table2 from  database1.table1

推荐答案

听起来像您的意思:

Sounds like you mean to do this:

--CREATE TABLE [database2].[table2](
--   [Idx][int]IDENTITY(1,1),
--      [data][nvarchar](256)

INSERT INTO [database2].[table2]
    SELECT * FROM [database1].[table1]

请注意，如您在帖子中所建议的那样，如果这两个表位于不同的数据库上，则使用这种类型的选择可能不起作用.

在BOL中查找LINKED SERVER和OPENDATASOURCE.必须启用分散的数据库才能相互查看，并且必须使用不同的语法才能对表进行查询.

简短的答案.


诀窍在于启用.像这样:

Be aware that if these two tables are on different databases, as you sugggest in the post, using this type of select might not work.

Look up LINKED SERVER and OPENDATASOURCE in the BOL. Disperate databases have to be enabled in order to see each other and they have to use a different syntax in order to make query on a table possible.

Short answer.


The trick is in enable. Like this:

USE [database2](you ARE missing SCHEMA, by the way)
EXEC sp_addlinkedserver N''(computername)\(instancename)'',
        N''SQL Server''

然后执行以下查询:

Then do the query like this:

INSERT INTO [database2].[table2]
   SELECT * FROM OPENQUERY([(computername)\(instancename)],''SELECT * FROM [database1].[table1]'')

[END EDIT]

[END EDIT]

这篇关于如何使用SQL将一个表从database1复制到database2?我的代码结果最终丢失了主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！