代码改变世界

Migration data on SQL

2015-05-20 11:03  caixia9  阅读(172)  评论(0)  编辑  收藏  举报

从表里面导出数据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