Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Different Options for Importing Data into SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (25)   |   Related Tips: 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


Last Update: 10/29/2012


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, September 29, 2014 - 8:48:28 PM - JaY Read The Tip

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 Read The Tip

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 09, 2014 - 9:13:13 AM - Kevin Read The Tip

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 09, 2013 - 6:29:04 AM - Santosh Menon Read The Tip

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 07, 2013 - 1:37:23 PM - Sean DeYoung Read The Tip

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 Read The Tip

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


Wednesday, April 24, 2013 - 4:34:14 PM - Peter Hummel Read The Tip

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 Read The Tip

@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 Read The Tip

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 Read The Tip

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 Read The Tip

Greg,

Thanks for the over view.  It helped nicely.

Tom B


Wednesday, January 30, 2013 - 8:49:21 PM - SungWook Kang Read The Tip

 

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 09, 2012 - 3:49:29 AM - Ramon Read The Tip

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 08, 2012 - 5:01:14 PM - Greg Robidoux Read The Tip

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


Thursday, November 08, 2012 - 3:49:57 PM - Ramon Read The Tip

Hi again

 

I've solved the problem:

INSERT INTO dbo.ImportTest 

SELECT * FROM ImportData...[Tabla1$]

Thanks.


Thursday, November 08, 2012 - 3:42:30 PM - Ramon Read The Tip

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

 

;-)

 

 


Thursday, November 08, 2012 - 3:37:39 PM - Greg Robidoux Read The Tip

@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 08, 2012 - 2:06:12 PM - Ramon Read The Tip

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 06, 2012 - 10:01:39 AM - Greg Robidoux Read The Tip

@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 06, 2012 - 9:59:05 AM - Greg Robidoux Read The Tip

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


Monday, November 05, 2012 - 11:56:28 PM - Shahzad Read The Tip

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 04, 2012 - 11:33:58 AM - Nagesh Read The Tip

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 Read The Tip

@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 Read The Tip

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 03, 2012 - 8:28:03 AM - Adi Read The Tip

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




 
Sponsor Information