The flow of what I want to achieve is as per the below steps:
I have an Excel file (which has dynamic columns), however certain columns are static and don't change, however their position in the file changes
I have a table that was created based on the static fields in a specific sequence (let's call that table
tblBase)I wish to upload the data from the Excel file into a
##temptable, and then from the##temptable append the data to existing tabletblBase, however only data from those columns which have the same name in both the table (not considering the position the sequence of the columns)
My code:
USE [master]
GO
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
SELECT *
INTO ##temp
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Users\NEW_01012023\Downloads\Report_SERVICES.xlsx; HDR=YES; IMEX=1',
'SELECT * FROM [Base$]')
GO
-- This throws an error as the columns are not mapped
-- and the table was created from a file which had the
-- column names with in a specific sequence
INSERT INTO tblBase
SELECT *
FROM ##temp
An example is below
Excel file has the following name (let's call it basefile)
ID,
Entry Date,
First Name,
Last Name,
Portfolio Unit,
Demand State,
Demand Type,
Staffed Date
tblBase [initially created on the above base1)
ID,
Entry Date,
First Name,
Last Name,
Portfolio Unit,
Demand State,
Demand Type,
Staffed Date
Next basefile has the same fields however in the following sequence: (let's call it base2)
ID,
Entry Date,
Staffed Date,
Last Name,
First Name,
Demand State,
Demand Type,
Portfolio Unit
My challenge is to insert the data from the ##temp table that was created using OPENROWSET based on table base1 and now insert / append the data from the ##temp table that is created using OPENROWSET based on table base2.