-- Description:Builds an insert-select statement from two table object_ids for a pair of temporary tables
CREATE
Function [dbo].[gen_InsertSelect] (@From_Object_Id int, @Into_Object_Id int) returns nvarchar(max)
AS
BEGIN
DECLARE @SQL nvarchar(max);
DECLARE @Into_TableName nvarchar(max)
DECLARE @From_TableName nvarchar(max)
DECLARE @Cols TABLE ([Name] nvarchar(100) NULL, [Id] int NULL)
;
SELECT @From_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @From_Object_id;
SELECT @Into_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @Into_Object_id;
----------------------------------------------------------------------------------------------------------------------------------------
-- Get list of common column names
INSERT INTO @Cols SELECT i.[Name], i.[Column_Id]
FROM tempdb.sys.columns i JOIN tempdb.sys.columns f
ON i.object_id = @Into_Object_Id and f.object_id = @From_Object_Id and i.Name=f.Name
;
-- convert list of names into a comma seperated string
SET @SQL = substring((SELECT ( ', ['+[NAME]+']' ) FROM @ColS b ORDER BY [Id] FOR XML PATH( '' )), 3, 4000 )
;
IF len(@SQL)>0 SET @SQL ='INSERT INTO '+@Into_TableName+' ('+@SQL+') SELECT '+@SQL+' FROM '+@From_TableName
;
RETURN @SQL
;
END