将表数据从一个 SQL Server 导出到另一个

2022-01-01 00:00:00 sql export sql-server-2005 sql-server

我有两个 SQL Server(都是 2005 版本).

I have two SQL Servers (both 2005 version).

我想将多个表从一个迁移到另一个.

I want to migrate several tables from one to another.

我试过了:

  • 在源服务器上，我右键单击了数据库，选择了Tasks/Generate scripts.问题是在 Table/View options 下没有 Script data 选项.

  • On source server I have right clicked on the database, selected Tasks/Generate scripts. The problem is that under Table/View options there is no Script data option.

然后我使用 Script Table As/Create script 来生成 SQL 文件，以便在我的目标服务器上创建表.但我仍然需要所有数据.

Then I used Script Table As/Create script to generate SQL files in order to create the tables on my destination server. But I still need all the data.

然后我尝试使用:

SELECT * 
INTO [destination server].[destination database].[dbo].[destination table] 
FROM [source server].[source database].[dbo].[source table]

但我收到错误:

对象包含超过最大数量的前缀.最大值是2.

Object contains more than the maximum number of prefixes. Maximum is 2.

有人可以指出我问题的正确解决方案吗?

Can someone please point me to the right solution to my problem?

推荐答案

试试这个:

  1. 使用 Script Table As/Create Script 步骤中的脚本在目标服务器上创建表

  1. create your table on the target server using your scripts from the Script Table As / Create Script step

在目标服务器上，然后您可以发出 T-SQL 语句:

on the target server, you can then issue a T-SQL statement:

INSERT INTO dbo.YourTableNameHere
   SELECT *
   FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere

这应该可以正常工作.

相关文章