Load excel data into existing SQL Server table

Question

Hi,

Can someone please suggest a way to load below excel data into existing SQL Server table. The format of the excel is different to the table columns as shown below.

TIA for your help.

Excel:

Existing Table in SQL Server (13.0.5850.14):

Answer 1

You can use a SSIS = "Integration Services" package with a Unpivot transformation to get the data in the required format.

Answer 2

Hi Shamk I´ve found this https://stackoverflow.com/questions/39610133/how-to-import-an-excel-file-into-sql-server don´t know if it can be useful to you.

Answer 3

Hi @ShamK ,

1.We can use Unpivot Transformation in SSIS package.
Please refer to the following pictures:

2.May I know which columns in Unpivot Output do you want to load into the existing table?

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

use OPENROWSET for load selected rows to sql server
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15