Reputation: 1
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 ##temp
table, and then from the ##temp
table append the data to existing table tblBase
, 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.
Upvotes: 0
Views: 49