How can I fill SQL Server table from excel only using sql query?

Question

How can I do that with Microsoft.ACE.OLEDB.12.0?

CREATE TABLE [dbo].[Addresses_Temp] ( 
    [FirstName]   VARCHAR(20), 
    [LastName]    VARCHAR(20), 
    [Address]     VARCHAR(50), 
    [City]        VARCHAR(30), 
    [State]       VARCHAR(2), 
    [ZIP]         VARCHAR(10) 
) 
GO 

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] ) 
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP] 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                'Excel 12.0;Database=C:\Source\Addresses.xlsx;IMEX=1', 
                'SELECT * FROM [Sayfa1$]')

How can I do that?

Answer 1

If this is only a one time execution, then you could just copy and paste into Sql Management Studio. Just make sure the columns match up correctly.

Answer 2

That technique can work if the server can see the file (and the path is relative to the server). Also, if it's on a share (or locally), then the account the server is acting under when it attempts to open the file needs to have sufficient rights to the share/directory/file.

The server also needs to have whatever provider you specify.

Are you seeing a particular error?