Search code examples
sql-serverexcelsql-server-2012

How to insert into table from csv/txt without using bulk insert?

Is there any other way to insert into table from csv/text file except bulk insert? By using query and not import by wizard.

Solution

  • Another way you can do it which I have used when importing CSV data into a SQL database is via linked servers, if you install the Microsoft Access Database Engine 2010 Redistributable onto your SQL server (make sure to use the correct x86/x64 version for your architecture):

    Microsoft Access 2010 Database Engine Redistributable

    Then you can define a linked server like this:

    EXEC master.dbo.sp_addlinkedserver @server = N'CSVLinkedServer', @srvproduct=N'OLEDB Provider for ACE', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'S:\csv_location\', @provstr=N'Text', @catalog=N'*.csv'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CSVLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    Once you have that, any CSV file that you put into the folder S:\csv_location\ ends up as a table named filename#csv inside the default catalogue for your linked server.

    I have a feeling that use of this particular provider isn't supported for this purpose by Microsoft, but we've found this to work brilliantly for data imports without having to resort to SSIS etc.