Dynamic sql generation script for insert into select from table

Hello everyone,

I'm attempting to create a script that dynamically generates one or more "insert into" statements with all columns properly defined (SQL Server 2012).

Here's what I have so far:

use source_db;
GO

create table table1
(
	column1 int
	,column2 varchar(10)
	,column3 datetime
);

create table table2
(
	column1 int
	,column2 varchar(10)
	,column3 datetime
);


use target_db;
GO

Declare @Tables as Table
(
	TableName	Varchar(50)
);

insert into @Tables 
(TableName)
select TableName 
from
(        
		SELECT 'table1' as TableName       
		UNION ALL SELECT 'table2'							
)a

declare @SQL nvarchar(max);

select @SQL = COALESCE(@SQL + '
', '')
+ CASE WHEN ROW_NUMBER() OVER(PARTITION BY a.TableName ORDER BY c.column_id) = 1 and ROW_NUMBER() OVER(ORDER BY a.TableName, c.column_id) <> 1 THEN 
'		)

' 
ELSE ''
END
+ CASE WHEN ROW_NUMBER() OVER(PARTITION BY a.TableName ORDER BY c.column_id) = 1
	THEN
		'
		INSERT INTO [target_db].[dbo].['+a.TableName+']
		( 
			['+c.name+']'
	ELSE
	'			,['+c.name+'] '
END
FROM @Tables a
INNER JOIN source_db.sys.Objects o
on o.Name = a.TableName
INNER JOIN source_db..sys.Columns c
on c.object_id = o.object_id
INNER JOIN source_db..sys.Types t
on c.user_type_id = t.user_type_id
Where o.type = 'U'

print @SQL

Select allOpen in new window

That script puts out a result that looks like this:

INSERT INTO [target_db].[dbo].[table1]
(
     column1
     ,column2
     ,column3

)

INSERT INTO [target_db].[dbo].[table2]
(
     column1
     ,column2
     ,column3
)

Select allOpen in new window

As you can see, I'm halfway there.  But I'm having trouble figuring out how to code the "select" segment that goes against the source_db.  Any help is appreciated.

Thanks,
Glen