SQL Insert from DB1 to DB2 using JOIN on another table and avoid specifying column names

Question

I was searching before and couldn't find any answers.

Let's put a scenario where we have 2 tables:
- Customer
- Order

And 2 Databases:
- DB1
- DB2

Customer:

• ID int
• Name varchar
  

Order :

• ID int
• CustomerID int
  

DB1 and DB2 have the same tables structure and contains different data.
Now let's say i want to run this query:

 INSERT INTO
    DB1.dbo.Order
 SELECT
    ID, CustomerID
 FROM 
    DB2.dbo.Order AS db2Order
    LEFT JOIN
    DB2.dbo.Customer AS db2Cust
 ON 
    db2Order.CustomerID = db2Cust.ID

This query works as expected. Now let's put up a scenario where you have more than 20 columns on a table and you don't want to go through and specify all of them, I want to do something like that:

INSERT INTO
    DB1.dbo.Order
 SELECT
 (
    SELECT COLUMN_NAME
    FROM DB1.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = N'Order'
 )
 FROM 
    DB2.dbo.Order AS db2Order
    LEFT JOIN
    DB2.dbo.Customer AS db2Cust
 ON 
    db2Order.CustomerID = db2Cust.ID

Because we JOIN the tables we must specify the columns since they won't match with the Order table structure since we trying to insert, we must satisfy this exception:
Column name or number of supplied values does not match table definition.

Is there a workaround through this? I would be happy to hear if anyone have any idea please. Thanks for your time reading this!

Answer 1

Thanks to @GordonLinoff pointing out for me about dynamic SQL, here is my solution:

DECLARE @CustomerID SMALLINT = 201,
        @ID         SMALLINT = 14007

DECLARE @columns AS NVARCHAR(1500) = '',
        @params  AS NVARCHAR(1000),
        @query   AS NVARCHAR(2000)

SELECT @columns += COLUMN_NAME + ',' FROM DB1.dbo.Order.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Order'
SELECT @columns = SUBSTRING(@columns, 0, LEN(@columns))

SET @params = '@CustomerID smallint, @ID smallint'
SET @query = 'INSERT INTO 
    DB1.dbo.Order
SELECT
    %s
FROM 
    DB2.dbo.Order AS db2Order
    LEFT JOIN
    DB2.dbo.Customer AS db2Cust
ON 
    db2Order.CustomerID = db2Cust.ID
WHERE 
    db2Order.CustomerID = @CustomerID AND db2Cust.ID = @ID'
EXEC sp_addmessage 50001, 16, @query, NULL, NULL, 'replace'
SET @query = FORMATMESSAGE(50001, @columns)
EXEC sp_executesql @query, @params, @CustomerID=@CustomerID, @ID=@ID

I use the sp_addmessage in here so i can use the FORMATMESSAGE function since in SQL version < 2012, formatmessage func doesn't support to be used strings directly. Hope that helps someone!