跨数据库服务器，复制表结构及数据（procedure）
时间：2021-07-01 10:21:17
帮助过：15人阅读
[PIS]
GO
/****** Object: StoredProcedure [dbo].[SynchronizePDI] Script Date: 2020/3/28 20:54:51 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].
[SynchronizePDI]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT
ON;
-- Insert statements for procedure here
IF EXISTS(
SELECT * FROM tempdb..sysobjects
WHERE id
=object_id(
‘tempdb..#tempTableName‘))
DROP TABLE #tempTableName
SELECT ROW_NUMBER()
OVER(
ORDER BY Id)
AS TABLEINDEX,TableName,SynchronizeServer,SynchronizeTableName,PrimaryColumn
INTO #tempTableName
FROM SynchronizePDISetting t
DECLARE @tableIndex INT =1,
@maxTableIndex INT=0,
@tableName nvarchar(
100)
=‘‘,
@SynchronizeServer nvarchar(
100)
=‘‘,
@SynchronizeTableName nvarchar(
100)
=‘‘,
@PrimaryColumn nvarchar(
100)
=‘‘
SELECT @tableIndex=MIN(TABLEINDEX),
@maxTableIndex=MAX(TABLEINDEX)
FROM #tempTableName
WHILE(
@tableIndex<=@maxTableIndex)
BEGIN
SELECT @tableName=TableName,
@SynchronizeServer=SynchronizeServer,
@SynchronizeTableName = SynchronizeTableName,
@PrimaryColumn=PrimaryColumn
FROM #tempTableName
WHERE TABLEINDEX
=@tableIndex
DECLARE @SQL NVARCHAR(
4000),
@maxId int
IF object_id(
@tableName,N
‘U‘)
IS NULL
BEGIN
exec (
‘ select * into ‘+@tableName+‘ from ‘+@SynchronizeServer+‘.‘+ @SynchronizeTableName)
END
ELSE
BEGIN
Set @SQL=‘select @maxId = max(id) from ‘+@tableName
print @SQL
exec sp_executesql
@SQL,N
‘@maxId int output‘,
@maxId output
Set @SQL= ‘ insert into ‘+@tableName +‘ select * from ‘+@SynchronizeServer+‘.‘+ @SynchronizeTableName +‘ where id >‘+ convert(
nvarchar(
200),
@maxId)
+‘;‘
print @SQL
exec (
@SQL)
END
--删除重复数据
Set @SQL= ‘ delete from ‘+@tableName +‘ where id not in (select max(id) from ‘+@tableName +‘ group by ‘+ @PrimaryColumn+‘);‘
print @SQL
exec (
@SQL)
SET @tableIndex=@tableIndex+1
END
END
跨数据库服务器，复制表结构及数据（procedure）
标签：HERE style tput har object_id setting rom begin ===
