用Tablediff把表數據生成SQL腳本

use tempdb
go
--表test
if object_id('test') is not null
    drop table test
create table Test(ID int identity(1,1) constraint PK_Test primary key,Name nvarchar(100) constraint U_Test_Name unique,Memo nvarchar(max),CreateDate datetime)
insert Test select 'TestName1','備注1','2008-01-15 12:00'
insert Test select 'TestName2','備注2','2008-01-15 13:00'
go
--創建臨時表對比
create table Tmp_Test(ID int identity(1,1) primary key,Name nvarchar(100) unique,Memo nvarchar(max),CreateDate datetime)
go
查看用法:

http://technet.microsoft.com/zh-cn/library/ms162843.aspx

--在命令執行
--運行——cmd——"cd C:/Program Files/Microsoft SQL Server/90/COM" --指定tablediff.exe文件路徑
--實例名:Roy
--執行以下命令:
--tablediff -sourceserver "Roy" -sourcedatabase "tempdb" -sourceschema "dbo" -sourcetable "test" -sourceuser "sa" -sourcepassword "sa密碼" -destinationserver "Roy" -destinationdatabase "tempdb" -destinationschema "dbo" -destinationtable "Tmp_Test" -destinationuser "sa" -destinationpassword "sa密碼" -f "E:/TestData.sql" -o "E:/Testlog.txt"
go

--查看TestData

/*

-- Host: Roy
-- Database: [tempdb]
-- Table: [dbo].[Tmp_Test]
-- Column(s) Memo are not included in this script because they are of type(s) text, ntext, varchar(max), nvarchar(max), varbinary(max), image, timestamp, or xml. Columns of these types cannot be updated by tablediff utility scripts; therefore non-convergence of data can still occur after this script has been applied. If the tablediff utility output references any of these columns, you must update the columns manually if you want them to converge.
SET IDENTITY_INSERT [dbo].[Tmp_Test] ON
INSERT INTO [dbo].[Tmp_Test] ([CreateDate],[ID],[Name]) VALUES ('2008-01-15 12:00:00.000',1,'TestName1')
INSERT INTO [dbo].[Tmp_Test] ([CreateDate],[ID],[Name]) VALUES ('2008-01-15 13:00:00.000',2,'TestName2')
SET IDENTITY_INSERT [dbo].[Tmp_Test] OFF

*/

--查看Testlog
/*
Table [tempdb].[dbo].[test] on Roy and Table [tempdb].[dbo].[Tmp_Test] on Roy have 2 differences.
Fix SQL written to E:/TestData.sql.
Err    ID    Col
Src. Only    1   
Src. Only    2   
The requested operation took 0.2187038 seconds.

*/


--在查詢分析里查看
DECLARE @Test table(SQL varchar(max))
INSERT @Test EXEC xp_cmdshell 'Type E:/TestData.sql'
SELECT  SQL FROM @Test WHERE SQL IS NOT NULL AND SQL not like N'--%' and  SQL not like 'nce%'

--處理后的生成格式

/*
SET IDENTITY_INSERT [dbo].[Tmp_Test] ON
INSERT INTO [dbo].[Tmp_Test] ([CreateDate],[ID],[Name]) VALUES ('2008-01-15 12:00:00.000',1,'TestName1')
INSERT INTO [dbo].[Tmp_Test] ([CreateDate],[ID],[Name]) VALUES ('2008-01-15 13:00:00.000',2,'TestName2')
SET IDENTITY_INSERT [dbo].[Tmp_Test] OFF
*/

drop table Test,Tmp_Test

 

转载于:https://www.cnblogs.com/Roy_88/archive/2009/01/15/5463093.html