INSERT表合并sql生成_sql server_数据库_思韵闪耀

INSERT表合并sql生成

思韵闪耀

2012-10-28

0次


--合并表
--输出调成以文本格式显示结果
DECLARE pcur CURSOR FOR
SELECT a.name,a.object_id
FROM sys.tables a
WHERE name IN('bdRecordTrace',
'bdTrace',
'bmdCustomerAsk',
'bmdCustomerShipRep',
'bmdTelTrace',
'bdfollowrecord')

DECLARE @tableName VARCHAR(50),@object_id INT,@insertsql NVARCHAR(max)
OPEN pcur
FETCH NEXT FROM pcur INTO @tableName,@object_id
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @insertsql = ''
	--SET @insertsql = 'INSERT INTO dbo.' + @tablename + '( '
	SELECT @insertsql = @insertsql + ',' + c.name
	FROM sys.columns c
	WHERE c.object_id = @object_id
	ORDER BY column_id
	SET @insertsql =  '--'+@tablename + '--'
					  + CHAR(13)
					  +'INSERT INTO dbo.' + @tablename + '( ' + STUFF(@insertsql,1,1,'') + ')'
					  + CHAR(13)
					  +  'SELECT * '
					  + CHAR(13)
					  +'FROM [211].BRM_LVJIAN.DBO.' + @TableName + ' a'
					  + CHAR(13)
					  +'WHERE NOT EXISTS( SELECT 1 FROM dbo.'+@tableName+' b(NOLOCK) WHERE b.id = a.id )'
					  + CHAR(13)
					  + 'GO'
					  + CHAR(13)
	PRINT @insertsql
	FETCH NEXT FROM pcur INTO @tableName,@object_id
END
CLOSE pcur
DEALLOCATE pcur

上一篇： mongodb权威指南笔记

下一篇： sqlserver日志提醒

INSERT表合并sql生成

相关内容

热门资讯