Insert Data into SQL Table from an Excel Sheet

Question

if I have the following table in SQL,

Item  Desc   Type   Location    Quantity    Unit_Price

A     AAA     X        1           0          20.00
B     BBB     Y        2           0          10.00
B     CCC     X        2           0          50.00
C     DDD     Z        1           0         150.00
C     EEE     Y        3           0          70.00
D     FFF     Z        3           0          65.00

And the following Excel Sheet

Item    Location   Quantity
A          1           1
B          1           2
B          2           3
C          1          40
C          3         500
D          3          10

How do I insert these quantities in to the SQL table reading from my Excel table ?

Solution 1

To do this with T-SQL, you can follow this tutorial in detail and start by pulling the data into a temporary table, as shown in the following SELECT…INTO statement:

SELECT * INTO #ProductInfo
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES; IMEX=1;
   Database=C:\DataFiles\ProductData.xlsx',
   [vProduct$]);

Using the OPENROWSET in-build function to retrieve Excel data where the first argument is the name of the provider which can be one of two providers:

• Microsoft.Jet.OLEDB.4.0: Use on SQL Server 32-bit editions for Excel 2003 files (or earlier).
• Microsoft.ACE.OLEDB.12.0: Use on SQL Server 32-bit editions for Excel 2007 files (or later) or on SQL Server 64-bit editions for any Excel files.

The second OPENROWSET argument defines the provider string delimited by a semi-colon with the first part specifying the file type:

• For Excel ’97-2003 (.xls) files, use Excel 8.0.
• For Excel 2007-2010 (.xlsx) files, use Excel 12.0 Xml.
• For Excel 2007-2010 macro-enabled (.xlsm) files, use Excel 12.0 Macro.
• For Excel 2007-2010 non-XML binary (.xlsb) files, use Excel 12.0.

The second part of the argument specifies the file’s path and file name. The third argument is the name of the spreadsheet we want to access with the dollar sign ($) appended and enclosed in brackets, as in [Products$], for example.

Once you have the data into the temporary table #ProductInfo, you can then convert, filter the data as necessary using an inner join and then update the Quantity field:

UPDATE
    p
SET
    p.Quantity = temp.Quantity
FROM dbo.Product AS p
INNER JOIN #ProductInfo AS temp
   ON temp.Item = p.Item
   AND temp.Location = p.Location;

Solution 2

Right Click on Database -> Tasks -> Import data -> wizard will open

click on Next(Source wizard will open) and choose the "Microsoft Excel" option in DataSource dropdown and choose the excel path Click the "Next" button(Excel column and Table column should be same otherwise it won't insert).

enter destination details in the wizard,click on Next

click the Next button

Click the Next button and Finish