Migration data on SQL

从表里面导出数据XML:

-- export
declare @xml xml 
set @xml = (select * from (
select TableName = Schema, 
       xmlData = ( select * from [Schema] where id = 337 for xml auto, root(Schemas) )
union
select SchemaFile, 
       ( select Id, SchemaId,
                Data = convert(varchar(max), Data),
                FileName, Fullpath, RelativePath
         from SchemaFile where SchemaId = 337 for xml auto, root(SchemaFiles) ) ) SchemaExport
for xml auto, root(SchemaExports)      )

select @xml

将导出XML数据插入到目的表中:

 1 -- now import
 2 
 3 declare @idoc int
 4 
 5       declare @tableName varchar(255)  
 6       declare @xmlData xml                
 7       
 8       declare @mappingExport table (TableName varchar(255), xmlData xml)
 9 
10       exec sp_xml_preparedocument @idoc output, @xml
11       insert into @mappingExport
12       select *
13       from openxml (@idoc, /SchemaExports/SchemaExport)
14       with (TableName varchar(255),
15               xmlData nvarchar(max))
16       exec sp_xml_removedocument @idoc output 
17       
18       declare exportCursor cursor
19       for select TableName, xmlData 
20       from @mappingExport
21 
22       open exportCursor
23 
24       fetch from exportCursor into @tableName, @xmlData
25 
26       while @@fetch_status <> -1
27       begin
28             exec sp_xml_preparedocument @idoc output, @xmlData
29                   
30             if (@tableName = Schema)
31             begin
32                   print Copying Schema 
33 
34                   select *
35                   from openxml (@idoc, /Schemas/Schema)
36                   with (Id int,
37                         TenantId int,
38                         VersionNo int,
39                         ResponseSchemaId int,
40                         SchemaName nvarchar(200),
41                         SOAPActino varchar(200),
42                         LastUpdatedUTC datetime,
43                         IsCanonical bit,
44                         DocumentType nvarchar(512) )
45             end
46             else if (@tableName = SchemaFile)
47             begin
48                   print Copying SchemaFile
49                   select *, datalength(data), convert(xml, data)
50                   from openxml (@idoc, /SchemaFiles/SchemaFile)
51                   with (Id int,
52                         SchemaId int,
53                         Data varchar(max),
54                         FileName nvarchar(255),
55                         FullPath nvarchar(255),
56                         RelativePath nvarchar(255) )
57             end
58             exec sp_xml_removedocument @idoc 
59             fetch next from exportCursor into @tableName, @xmlData            
60       end   
61       
62 select datalength(data), convert(xml, data)
63 from schemafile
64 where schemaid = 337

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。