Snehal Panchal
Snehal Panchal

Reputation: 1

Create ##temp table and insert the rows to an existing table only wherever the columns match

The flow of what I want to achieve is as per the below steps:

  1. 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

  2. I have a table that was created based on the static fields in a specific sequence (let's call that table tblBase)

  3. 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

Answers (0)

Related Questions