insert into TableB (col1, col7, col11)
select colA, colK, colC from TableA;

insert into TableB (col1, col2, col3, col4)
select colA, 'abcd',null,ColC from TableA

DECLARE @t1 varchar(255), @t2 varchar(255)
SELECT @t1 = 'myMessage', @t2 = 'User_Message'

SELECT SQL FROM (
SELECT 'INSERT INTO ' + TABLE_NAME + ' ( ' AS SQL
, 1 AS SQL_CODE, 1 AS ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @t1
UNION ALL
SELECT ' ' + COLUMN_NAME AS SQL
, 2 AS SQL_CODE, ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Columns co
WHERE TABLE_NAME = @t1
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t2
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME)
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t1
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME
			   GROUP BY ci.COLUMN_NAME
			   HAVING co.ORDINAL_POSITION = MIN(ci.ORDINAL_POSITION))
			   
UNION ALL
SELECT ',' + COLUMN_NAME AS SQL
, 2 AS SQL_CODE, ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Columns co
WHERE TABLE_NAME = @t1
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t2
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME)
AND NOT EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t1
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME
			   GROUP BY ci.COLUMN_NAME
			   HAVING co.ORDINAL_POSITION = MIN(ci.ORDINAL_POSITION))
UNION ALL
SELECT ')' AS SQL
, 3 AS SQL_CODE, 1 AS ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @t1			   
UNION ALL
SELECT 'SELECT  ' AS SQL
, 4 AS SQL_CODE, 1 AS ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @t2
UNION ALL
SELECT ' ' + COLUMN_NAME AS SQL
, 5 AS SQL_CODE, ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Columns co
WHERE TABLE_NAME = @t2
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t1
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME)
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t2
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME
			   GROUP BY ci.COLUMN_NAME
			   HAVING co.ORDINAL_POSITION = MIN(ci.ORDINAL_POSITION))
			   
UNION ALL
SELECT ',' + COLUMN_NAME AS SQL
, 6 AS SQL_CODE, ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Columns co
WHERE TABLE_NAME = @t2
AND EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t1
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME)
AND NOT EXISTS (SELECT * 
			  FROM INFORMATION_SCHEMA.Columns ci 
			 WHERE TABLE_NAME = @t2
			   AND ci.COLUMN_NAME  = co.COLUMN_NAME
			   GROUP BY ci.COLUMN_NAME
			   HAVING co.ORDINAL_POSITION = MIN(ci.ORDINAL_POSITION))
UNION ALL
SELECT '  FROM ' + TABLE_NAME AS SQL
, 7 AS SQL_CODE, 1 AS ORDINAL_POSITION, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @t2
) AS XXX

quote:

---

Originally posted by WJHamel

Thank you. If my noncommon columns in tableB are all nullable, then i don't need to use option 2, correct?

---