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?

Are you trying to do this from sqlserver directly?

Gratzy
– Gratzy

2010-06-11 15:06:28 +00:00
Commented Jun 11, 2010 at 15:06 — Gratzy
– Gratzy, Commented Jun 11, 2010 at 15:06

Cade Roux · Accepted Answer · 2010-06-12 04:28:37Z

1

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?

answered Jun 12, 2010 at 4:28

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related