如何将数据从一个表复制到另一个服务器上的另一个表?

I have a simple SQL that on a production database finds about 3000 rows:

我有一个简单的SQL，在一个产品数据库中找到大约3000行:

SELECT *
FROM [CONTOSO].[BATID] (NOLOCK)
WHERE COMPANYNO = 1
AND MEASURINGPOINT = '592-98901_NPT'
AND TIMESERIESNO = 1
AND DATE_TIME >= '2013-01-31 23:00:00'
AND DATE_TIME <= '2013-02-28 22:59:00'
ORDER BY DATE_TIME

Is there some way that I can convert the output into some kind of big 'INSERT INTO....'-sql that would insert this data into the same table. Then I would easily be able to copy the sql into the other SQL Server Management Studio-window and execute it.

有一些方法,我可以将输出转换成某种大的插入....-sql将这些数据插入到同一个表中。然后我就可以很容易地将sql复制到另一个sql Server Management studio窗口并执行它。

6 个解决方案

#1

If your servers are not connected, you can use the SSMS import/ export wizard. This export creates a data file that you can then import at the remote server.

如果您的服务器没有连接，您可以使用ssm导入/导出向导。这个导出创建一个数据文件，您可以在远程服务器上导入它。

#2

use INSERT INTO..SELECT

使用插入…选择

INSERT INTO TableName (collName1, colName2,...)
SELECT * -- this should match to the names of column define in the INSERT clause
FROM [CONTOSO].[BATID] (NOLOCK)
WHERE COMPANYNO = 1
AND MEASURINGPOINT = '592-98901_NPT'
AND TIMESERIESNO = 1
AND DATE_TIME >= '2013-01-31 23:00:00'
AND DATE_TIME <= '2013-02-28 22:59:00'

#3

If create table as select is available in your version of SQL then you can use this:

如果在您的SQL版本中可以使用create table as select，那么您可以使用以下方法:

Create table tab_name AS
 SELECT... your query...

#4

SSMS Tools Pack has a great Generate Insert Statements function: http://www.ssmstoolspack.com/ It allows you to easily enter the where clause to get just the data you want to export. This used to be totally free but now requires a small fee if using with SSMS 2012+

SSMS工具包有一个很好的生成Insert语句功能:http://www.ssmstoolspack.com/它允许您轻松地输入where子句，以获得您想要导出的数据。这以前是完全免费的，但现在使用SSMS 2012+需要少量费用

If you have more money and need to do this kind of thing a lot, then Red Gate's SQL Data Compare is a great tool: https://www.red-gate.com/products/sql-development/sql-data-compare/

如果你有更多的钱并且需要做很多这样的事情，那么Red Gate的SQL数据比较是一个很好的工具:https://www.red-gate.com/products/sql-development/sql- Data compare/

#5

Also you can create Linked Server and then copy data from one table to another table on Linked Server.

还可以创建链接服务器，然后在链接服务器上将数据从一个表复制到另一个表。

sp_addlinkedserver  
  @server= N'NameofLinkedServer',
  @srvproduct= N'',
  @provider= N'SQLNCLI',
  @datasrc= N'YourRemoteServer';

sp_addlinkedsrvlogin
  @rmtsrvname = 'NameofLinkedServer' ,
  @useself = 'FALSE' ,
  @locallogin = 'local_login' ,
  @rmtuser = 'remote_login' ,
  @rmtpassword = 'remote_password'

After creating Linked Server execute this INSERT statement

创建链接服务器后，执行这个INSERT语句

INSERT [NameofLinkedServer].[your_databaseName].[CONTOSO].[BATID](Column1, Column2, ...)
SELECT (Column1, Column2, ...)
FROM [CONTOSO].[BATID] (NOLOCK)
WHERE COMPANYNO = 1
AND MEASURINGPOINT = '592-98901_NPT'
AND TIMESERIESNO = 1
AND DATE_TIME >= '2013-01-31 23:00:00'
AND DATE_TIME <= '2013-02-28 22:59:00'
ORDER BY DATE_TIME

For details, see the MSDN docs

有关详细信息，请参见MSDN文档。

#6

You can write code that creates code.

您可以编写创建代码的代码。

I 've done it for the "MembershipProvider" database here: http://granadacoder.wordpress.com/2007/11/29/membershipprovider-helper-to-transfer-data/

我已经在这里为“MembershipProvider”数据库做过了:http://granadacoder.wordpress.com/2007/11/29/membershipprovider- helperto -transfer-data/

You can modify to fit your needs.

您可以修改以适应您的需要。

Watch the double and single quotes, they'll throw a monkey wrench in the plans.

注意双引号和单引号，它们会破坏计划。

Here is a preview of the bigger code example:

下面是更大的代码示例的预览:

Note, it creates code that can be run on the second sql server.

注意，它创建了可以在第二个sql server上运行的代码。

    select 
    'INSERT INTO dbo.aspnet_Applications ( ApplicationName,LoweredApplicationName,ApplicationId,[Description] ) values ('  as [--Comment], 
    char(39) + t1.ApplicationName + char(39) ,  ',' , 
    char(39) + t1.LoweredApplicationName + char(39) ,  ',' , 
    char(39) + convert(varchar(38)  , t1.ApplicationId ) + char(39) ,  ',' , 
    char(39) + t1.Description + char(39) 
    , ')'
     FROM
        dbo.aspnet_Applications t1

Here is a Northwind example that handles single quotes in the CompanyName and Address columns, and has a "where not exists" check on the PrimaryKey. Note, this table did not have any ints/numbers, but simply removing the single quotes will work. If you want to check for null values, that's a simple case statement as seen in the Fax column.

这里有一个Northwind示例，它处理公司名称和地址列中的单引号，并对PrimaryKey进行“不存在的地方”检查。注意，这个表没有任何int /number，但是简单地删除单引号就可以了。如果要检查空值，这是一个简单的case语句，如传真列所示。

Select 'INSERT INTO dbo.Customers ( CustomerID,CompanyName,ContactName,ContactTitle,[Address],City,Region,PostalCode,Country,Phone,Fax ) '   as [--Comment], 
'SELECT ' , 
char(39) + t1.CustomerID + char(39) ,  ',' , 
char(39) + REPLACE(t1.CompanyName , char(39) , char(39) + '+' + 'char(39)' + '+' +  char(39) ) + char(39) ,  ',' , 
char(39) + t1.ContactName + char(39) ,  ',' , 
char(39) + t1.ContactTitle + char(39) ,  ',' , 
char(39) + REPLACE(t1.[Address] , char(39) , char(39) + '+' + 'char(39)' + '+' +  char(39) ) + char(39) ,  ',' , 
char(39) + t1.City + char(39) ,  ',' , 
char(39) + t1.Region + char(39) ,  ',' , 
char(39) + t1.PostalCode + char(39) ,  ',' , 
char(39) + t1.Country + char(39) ,  ',' , 
char(39) + t1.Phone + char(39) ,  ',' , 
Fax = case
    when t1.Fax is null then 'null'
    else char(39) + t1.[Fax] + char(39) 
    end
, ' Where not exists (select null from dbo.Customers innerC where innerC.CustomerID = ' + char(39) + t1.CustomerID + char(39) + ')'
 FROM
dbo.Customers t1

#1