Importing the Excel into the Database (SQL).

We can import the excel into the Database by using the following query.

Suppose we have a table named as Address_Temp and it has three column.

1. Name

2. State

3. Zip

Here we need to update the zip code with the new value and to update that zip code we need to check the excel.

So we can use that excel in our sql server by importing it using the following command.

Its very easy query but make sure that it won't work in following two scenarios.

1. The file need to be in local machine.

2. The file should not be password protected.

Declare @ZipTemp NVarchar(255)   

set @ZipTemp = (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Addresses.xls;IMEX=1','SELECT top 1 ZIP FROM [Sheet1$] where ZIP = "328301"'))
Update [dbo].[Addresses_Temp]
set State = 'CA', Zip = @ZipTemp 
where Zip = '328301'`

Here IMEX stands for mixed data type.