Different Options for Importing Data into SQL Server

By:   |   Updated: 2012-10-29   |   Comments (31)   |   Related: 1 | 2 | More > Import and Export


Problem

Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis.  One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use SSIS.  In this tip we take a look at some of these other options for importing data into SQL Server.

Solution

In addition to using the Import / Export wizards or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server.  Some these other options include bcp, BULK INSERT, OPENROWSET as well as others.  The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

BCP

This is one of the options that is mostly widely used.  One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command.  This command allows you to both import and export data, but is primarily used for text data formats.  In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

For more information about bcp click here.

BULK INSERT

This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL.  This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

For more information about BULK INSERT click here.

OPENROWSET

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server.  By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest.   The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

For more information about OPENROWSET click here.

OPENDATASOURCE

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

For more information about OPENDATASOURCE click here.

OPENQUERY

Another option is OPENQUERY.  This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table.  There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command.  This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

For more information about OPENQUERY click here.

Linked Servers

Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server.  This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

For more information about Linked Servers click here.

As you can see right out of the box SQL Server offers many ways of importing data into SQL Server.  Take a look at these different options to see what satisfies your database requirements.

Next Steps
  • Explore these different options to see what works best for your needs.
  • Take a look at these related tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-10-29

Comments For This Article




Friday, October 6, 2017 - 3:31:40 AM - KIm Back To Top (66966)

 Hi,

I am a new in MSSQL / database world. Could you guys guide me on how to convert data from Oracle 9 to MSSQL. Currently the oracle database in .DAT file format and I have Oracle program/application in my test environment. Please advise me on how to convert it.

Thanks,

 

Thursday, April 13, 2017 - 11:24:38 AM - Manuel Back To Top (54803)

 Hi, Trying to insert tab delimited text file to SQL server with bulk insert program. The data get inserted but the quotes around the data is also inserted is there any way i can remove the quotes when bulk inserting, other than writing script with replace command.

Please reply

Thanks

 

 

Friday, March 10, 2017 - 1:37:45 AM - sangeeta Back To Top (47715)

 Hi,

I am trying to import excel data (9 lakhs record) into sql server database table and i tried it through BCP but while importing the data format changed into binary language, can someone please suggest what other options i can use to import excel data (which is not time consuming).

 

Thanks in advance!!

 

Sunday, June 19, 2016 - 8:27:28 AM - Greg Robidoux Back To Top (41722)

Hi Nutan,

I have not tried openrowset with a file with that many records.  You can test it out to see how it works.

BCP is very fast at importing data.

And you could use the options you mentioned as well.

-Greg

Saturday, June 18, 2016 - 10:53:26 PM - Nutan Patel Back To Top (41719)

Does below method works well with larger files (more than 15000 rows) ? 

INSERT INTO dbo.ImportTest 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Or I Should use SSIS or SQL Import Export Wizard to performa operations ?

Thanks

Tuesday, May 5, 2015 - 7:29:21 AM - SRIKANTHMS Back To Top (37094)

i ENCOUNTRED THE FOLLOWING ISSUE

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

WHILE importing the excel file. how to resolve it.

Monday, September 29, 2014 - 8:48:28 PM - JaY Back To Top (34762)

Hi Greg,


Very informative article I really enjoyed and I have quick question recently I was working on IMPORT AND EXPORT WIZARD in Management studio for some limited amount of Data transfer between 2 servers. My main Concern is , Is there a way in IMPORT AND EXPORT WIZARD to transfer data in ROW LEVEL , I mean just couple rows or 5 rows in a 10 row table . ?? Or something like that !!! If not what will be the ideal solution for this kind of Data Transfer apart from writing SQL QUERY ?


Thanks a lot , Waiting for your response

Monday, March 24, 2014 - 6:35:40 PM - chimpinano Back To Top (29862)

Try this

 

EXEC DATABASE_NAME..xp_cmdshell 'bcp "select ''id'' as id, ''detail'' as detail, ''creation_date'' as creation_date

UNION ALL

select convert(varchar,id), detail, convert(varchar,creation_date) from DATABASE_NAME.dbo.TABLE" queryout "C:\file.txt" -c -t, -r \n -T -S "ServerName"'

 

Thursday, January 9, 2014 - 9:13:13 AM - Kevin Back To Top (28003)

We are running SQL Server 2012 EE on Windows Server 2008 R2 EE. In SQL Server, we have set up linked servers which links to our unix/oracle 10g instance.


I was able to load Oracle Data (non xml) into SQL Server using the Linked Server method with the OpenQuery Function and pass-through query. However, I have discovered (read) that xml data types are not supported in distributed queries (linked servers). 


So, I then tried using SSIS. I created a package, I created a Connection Manager using the "Native OLE DB\Oracle Provider for OLE DB" Provider. I then went to the OLE DB Source Editor, selected the OLE DB Connection Manager, the Data Access Mode (Table or View) and the Name of the table or view (actual Oracle Table) and clicked the Columns Page or Preview Button. The following error was returned: 


Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E37 Description: "Table does not exist.". An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E37 Description: "Table does not exist.". An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ROW-00004: Invalid column datatype". 


This error was also returned when I used the "Native OLE DB\Microsoft OLE DB Provider for Oracle" Provider. 


Does anyone know of a way I can used the Microsoft SQL Server Products (2012) to load Oracle XML Datatypes into SQL Server without using a 3rd party product? We are using Oracle Client 11g Software.


After more research:


I got around the "Table does not exist" error by changing the Data Access Mode to SQL Command and using SQL Code to access the table without the double quotes. Now, when I click on the Preview Button, the following error is returned:


Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ROW-00004: Invalid column datatype". 

 

Thanks for your time and effort in advance, Kevin 

Monday, December 9, 2013 - 6:29:04 AM - Santosh Menon Back To Top (27737)

i have a xml file and a table i want to import the xml file and want to dump  in sql server is there any way for bulk import

Sunday, July 7, 2013 - 1:37:23 PM - Sean DeYoung Back To Top (25738)

This is really good information. I want to mentions two other methods that available through a wizard and a inprocess application.

 

* Import / Export Wizard through SQL Server Management Studio

* SQL Server Integrations Services

Thursday, May 23, 2013 - 8:54:31 AM - krishna Back To Top (25099)

thank u very much ur code is great its very help ful for me

Wednesday, April 24, 2013 - 4:34:14 PM - Peter Hummel Back To Top (23549)

we have pdf files sitting on drives on the server.  We need to print the contects of the pdf based on a record telling us where this file is located. We have created an SQL table where we can load images and then print using crystal however we don't know how to dynamically tell SQL where to get the file.  the location is in a table and we can get the path easily.

 

thanks.  Peter

Tuesday, April 16, 2013 - 10:26:20 AM - Greg Robidoux Back To Top (23377)

@Charan - are you using SQL Server or MySQL?  It looks like MySQL in your error message.

Tuesday, April 16, 2013 - 1:59:39 AM - Charan Chakravarthi Back To Top (23365)

Hi Greg,

I am trying to read read data out of an excel and place it in my local DB, with autopopulating Date information on when the sheet was inserted. I tried your approach in using a OPENROWSET to import the data, but I get the below error. 

11:18:04INSERT INTO CCVProd_Test.CCVProd  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  'Excel 8.0;Database=E:\Projects\STUBAI\Production_Tool_V0.02\CCVProdx.csv', 'SELECT SerialNo, BARCODETEST, DFUTEST, LEDTEST, BUZZERTEST, CONTACTLESSTEST, DCDCPOWERTEST, PUSHBUTTON, MAXBAUDRATETEST, BACKLIGHTTEST, CONTACTTEST, LCDTEST, SAMTEST, USBLOOPBACKTEST, WAKEINOUTTEST FROM [CCVProdx$]')Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Microsoft.Jet.OLEDB.4.0',  'Excel 8.0;Database=E:\Projects\STUBAI\Production_T' at line 20.000 sec

 

Please advice

Sunday, March 10, 2013 - 11:35:49 AM - Jose Lourenco Back To Top (22694)

Hi Greg,

I have a central SQL database in a server, and a replica in a remote laptop. I need to update a table (just one table) at the remote replica, by requesting new records that may exist at the central database, according to a filter (I do not want all new onew, just some, which I can filter in one of the fields). How can I do this?

is it possible to create a service that will do this automaticaly, say every 15min?

Thanks in advance,

Regards,

Jose Lourenco

Friday, February 15, 2013 - 10:10:57 AM - Tom B Back To Top (22156)

Greg,

Thanks for the over view.  It helped nicely.

Tom B

Wednesday, January 30, 2013 - 8:49:21 PM - SungWook Kang Back To Top (21810)

 

Hi. nice to meet you.

I'm from  korean. I do not speak English well

 So currently being used Google translator.

 

Ramon occurred is an issue.

 

Your test environment is 64-bit?

 

I test environment is 64-bit

 

[Error]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', [Sheet1$])

 

[Success]

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\test.xls', [Sheet1$])

 

 

than you.

 
 
 
Regards
SungWook

Friday, November 9, 2012 - 3:49:29 AM - Ramon Back To Top (20270)

Thanks Greg.

 

One question more about this.

 

If the Excel sheet it isn't in a local drive of the SQL Server, how can write the expression?

 

EXEC sp_addlinkedserver 'ImportData', 

   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 

   '\\HPNS09\Globdat\Apps\GCVerde\ForecastRS.xls', 

   NULL, 

   'Excel 8.0' 

GO 

 

INSERT INTO dbo.ImportTest 

SELECT * FROM ImportData...[Tabla$]

 

I get a new error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData".

 
Some suggestions?
 
Regards
Ramón

 

Thursday, November 8, 2012 - 5:01:14 PM - Greg Robidoux Back To Top (20266)

Great glad you got this to work.  Any thanks for teaching me a little spanish. :)

Thursday, November 8, 2012 - 3:49:57 PM - Ramon Back To Top (20265)

Hi again

 

I've solved the problem:

INSERT INTO dbo.ImportTest 

SELECT * FROM ImportData...[Tabla1$]

Thanks.

Thursday, November 8, 2012 - 3:42:30 PM - Ramon Back To Top (20264)

Sure, Greg Tabla it's Table in spanish....

 

;-)

 

 

Thursday, November 8, 2012 - 3:37:39 PM - Greg Robidoux Back To Top (20263)

@Ramon - are you sure you have the correct name of the worksheet in the Excel file?

It looks like you have Tabla1$ should this be Table1$?

Thursday, November 8, 2012 - 2:06:12 PM - Ramon Back To Top (20262)

Thank you very much Greg, nice article.

 

One question. If I use this code:

EXEC sp_addlinkedserver 'ImportData', 

   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 

   'C:\temp\Libro1.xls', 

   NULL, 

   'Excel 8.0' 

GO 

 

INSERT INTO dbo.ImportTest 

SELECT * FROM ImportData...Tabla1$

I get this error:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData" does not contain the table "Tabla1$". The table either does not exist or the current user does not have permissions on that table.

The c:\temp\Libro1.xls it's in the c: drive of the SQL Server.
 
What is wrong?
 
Thanks in advance

 

 

Tuesday, November 6, 2012 - 10:01:39 AM - Greg Robidoux Back To Top (20229)

@Nagesh - you can query objects from other databases on the same instance by using a three part naming convention:

SELECT * FROM dbName.objectOwner.object

If you have the data on another server, you can setup a linked server and then use a four part naming convention:

SELECT * FROM linkedServer.dbName.objectOwner.object

http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/

Tuesday, November 6, 2012 - 9:59:05 AM - Greg Robidoux Back To Top (20228)

@Shahzad - this error could be caused by serveral things.  Are you able to read any Excel file using this approach?

Monday, November 5, 2012 - 11:56:28 PM - Shahzad Back To Top (20221)

Hello, Gregwhen i execute the query to import data from excel sheet, i will return following error:
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData".

Sunday, November 4, 2012 - 11:33:58 AM - Nagesh Back To Top (20207)

My dear leads,

Iam looking for a script regarding to huge count of tables movement from one database to another in the same instance/another instance database(other database already having objects).

 

So requesting u all pls help me in this case.

 

Nagesh

+91-8861713766

Tuesday, September 18, 2012 - 11:49:54 AM - Greg Robidoux Back To Top (19551)

@deepankar

I guess it depends on how complicated things are and how much the data changes. 

The advantage of SSIS is that you can connect to multiple data sources at one time and then run the package from any server as long as you have permissions to the databases.

The advantage to BCP is that you can write scripts and do not need to use Visual Studio.  So if things are not that complex or if the object structures are the same BCP might be an easier option.

As you can see I don't really have a preference it is really what is the easiest tool to use, what have you standardized and what tool do you know best.

Monday, September 17, 2012 - 9:20:31 AM - deepankar Back To Top (19531)

Hi Greg ,

Can you please suggest what should we better use when we need to copy database tables from one server to another (tables across different database servers are identical) on daily basis.

We are currently using BCP to complete the task but in order to provide flexibility we are looking for preparing a SSIS package instead which will anyhow use the BCP option.

 

By the way im a regular follower of your blogs.!! :-) and it has been a great learning experience for me.

Regards

deepankar

Sunday, June 3, 2012 - 8:28:03 AM - Adi Back To Top (17790)

Importing data using linked-server / openquery can sometimes cause a lot of headache. Not to mention the need to create a matching table on the destination side

Here's another quick option, which works great for even more complex data -

It's called ClipTable - a free tool that instantly turns any clipboard data into a SQL Table.

You can find it here - http://www.doccolabs.com/products_cliptable.html














get free sql tips
agree to terms