-- 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