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