如何将数据从sql server中的一个DB复制到另一个Sql server中的另一个DB? [英] How to copy data from one DB in sql server to another DB in another Sql server?
本文介绍了如何将数据从sql server中的一个DB复制到另一个Sql server中的另一个DB?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将数据从sql server中的一个DB复制到另一个Sql server中的另一个DB?我正在使用Archival SP实现相同的方法,其中来自Transaction的数据基于存档日期存档,而主表中的数据则不存档。我使用Linked Server在2个sql server实例中的DB之间进行数据传输。我希望SQL Server中的另一个SP脚本除了链接服务器之外还可以执行此操作。
我的档案SP如下:
How to copy data from one DB in sql server to another DB in another Sql server? I am using Archival SP to implement the same where datas from Transaction are archived based on the Archival Date while data in the Master Table isn't . I am using Linked Server for data transfer between the DB in 2 sql server instances. I want another script of SP in SQL Server to do this other than Linked Server.
My Archival SP is as follows:
CREATE PROCEDURE [dbo].[DIB_ARCHIVE]
(
@LIVEDB NVarchar(100)
, @ARCHIVEDB Varchar(100)
, @ArchiveDate VARCHAR(10)
, @ArchiveAgeDate VARCHAR(10)
)
AS
DECLARE @TableName Varchar(200)
Declare @DateField Varchar(50)
Declare @TableType char(1)
Declare @STRIDENTITY NVarchar(4000)
Declare @STRINSERT NVarchar(4000)
Declare @STRDELETE NVarchar(4000)
declare @ColumnList varchar(MAX)
declare @STRUSE Nvarchar(100)
declare @DISABLECONSTRAINT Nvarchar(1000) declare @ENABLECONSTRAINT Nvarchar(1000) Declare @Archive_LogID INT Declare @NumRowsChanged INT
SET @DISABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT SET @DISABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT
set @STRUSE='USE '+@LIVEDB
EXECUTE sp_executesql @STRUSE
---Archive LOg-------------
INSERT INTO Archive_Log (LiveDBName,ArchivalDBName,Archival_Date,ArchivalStartTime)
VALUES (@LIVEDB,@ARCHIVEDB,getdate(),getdate())
SET @Archive_LogID= SCOPE_IDENTITY() ---Archive LOg-------------
BEGIN TRY
BEGIN TRAN
IF CURSOR_STATUS('global','ARCHIVE')>=-1
BEGIN
DEALLOCATE ARCHIVE
END
DECLARE ARCHIVE CURSOR FOR SELECT TableName,DateField,TableType FROM dbo.Archive_config OPEN ARCHIVE fetch next from ARCHIVE into @TableName, @DateField,@TableType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STRIDENTITY=''
set @ColumnList=''
set @STRINSERT=''
--**********SQL to SET IDENTITY_INSERT ON**********--
IF Exists (select 1 from sys.all_columns c INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE is_identity = 1 AND t.type = 'U' and Object_name(t.object_id)=@TableName)
SET @STRIDENTITY='SET IDENTITY_INSERT '+ @ARCHIVEDB +'.dbo.'+@TableName + ' ON '
--**********SQL to SET IDENTITY_INSERT ON**********--
IF(@TableType='M') --Master Table
BEGIN
--**********Delete all rows from Master/Meta Table**********--
SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName
EXECUTE sp_executesql @STRDELETE
--**********Delete all rows from Master/Meta Table**********--
---Archive LOg-------------
SET @NumRowsChanged=@@ROWCOUNT
INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB : '+ @ARCHIVEDB )
---Archive LOg-------------
--**********Insert to Archive Table from Live Master/Meta Table**********--
select @ColumnList=@ColumnList+ ',' + column_name from information_schema.columns where
table_name = @TableName and DATA_TYPE not in ('timestamp')
set @ColumnList= RIGHT(@ColumnList,Len(@ColumnList)-1)
SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @ColumnList + ')
SELECT '+ @ColumnList + ' FROM '+ @LIVEDB +'.dbo.'+@TableName
EXECUTE sp_executesql @STRINSERT
--**********Insert to Archive Table from Live Master/Meta Table**********--
---Archive LOg-------------
SET @NumRowsChanged=@@ROWCOUNT
INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Master Table) : ' +@TableName +', Archival DB : '+ @ARCHIVEDB )
---Archive LOg-------------
END
ELSE IF(@TableType='T') --Transaction Table
BEGIN
--**********Insert to Archive Table from Live Master/Meta Table**********--
select @ColumnList=@ColumnList+ ',' + column_name from information_schema.columns where
table_name = @TableName and DATA_TYPE not in ('timestamp')
set @ColumnList= RIGHT(@ColumnList,Len(@ColumnList)-1)
SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @ColumnList + ')
SELECT '+ @ColumnList + ' FROM '+ @LIVEDB +'.dbo.'+@TableName + '
WHERE '+ @DateField + '
< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'
EXECUTE sp_executesql @STRINSERT
--**********Insert to Archive Table from Live Master/Meta Table**********--
---Archive LOg-------------
SET @NumRowsChanged=@@ROWCOUNT
INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB : '+ @ARCHIVEDB )
---Archive LOg-------------
--**********Delete rows from Master/Meta Table base on date range**********--
SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '
WHERE '+ @DateField + '
< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'
EXECUTE sp_executesql @STRDELETE
--**********Delete all rows from Master/Meta Table base on date range**********--
---Archive LOg-------------
SET @NumRowsChanged=@@ROWCOUNT
INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Transaction Table) : ' +@TableName +' ,Live DB : '+ @LIVEDB )
---Archive LOg-------------
END
FETCH NEXT
from ARCHIVE into @TableName, @DateField,@TableType END CLOSE ARCHIVE DEALLOCATE ARCHIVE COMMIT TRAN
---Archive LOg-------------
UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Successfully Completed '
WHERE Archive_LogID=@Archive_LogID
---Archive LOg-------------
PRINT 'Successfully Completed'
END TRY
BEGIN CATCH
ROLLBACK TRAN
---Archive LOg-------------
UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Archive Script Failed at Table : '+ @TableName +' , Error Message: ' + ERROR_MESSAGE()
WHERE Archive_LogID=@Archive_LogID
---Archive LOg-------------
PRINT 'Archive Script Failed at Table : '+ @TableName +' , Error Message: ' + ERROR_MESSAGE()
END CATCH
SET @ENABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @ENABLECONSTRAINT SET @ENABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @ENABLECONSTRAINT
推荐答案
INSERT [NewDB].[your_schema].[tablename](colum1 name,colum2 name)
SELECT colum1name,colum2name
FROM [OldDB].[your_schema].[tablename]
使用SSIS建立连接,然后就可以运行脚本了。你不需要链接服务器在那种情况下......
Use SSIS to establish connection and then you can run your script. You won't need linked server in that case...
这篇关于如何将数据从sql server中的一个DB复制到另一个Sql server中的另一个DB?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文