 |
|
SQL Product Highlight
Red Gate Software - SQL Developer Bundle
Improve database development with a bundle of 12 SQL developer tools from Red Gate. The SQL Developer Bundle will help you:
- Improve testing and productivity. Find and fix errors caused by differences between databases with SQL Compare.
- Troubleshoot errors. Browse a history of changes, find broken code or conflicts, and automatically generate scripts to resolve inconsistencies.
- Simplify database deployments. Use one-click deployment across multiple servers.
Learn more!
Different Options for Importing Data into SQL Server
|
By: Greg Robidoux | Read Comments (20) | 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.
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.
For more information about bcp click here.
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.
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )
For more information about BULK INSERT click here.
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.
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.
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.
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.
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.
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.
'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.
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.
'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
Print
Comments and Feedback:
| 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 |
|
| 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 |
|
| 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. |
|
| 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 |
|
| 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: |
|
| 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? |
|
| 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/ |
|
| 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
|
|
| 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 - 3:42:30 PM - Ramon | Read The Tip |
|
Sure, Greg Tabla it's Table in 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 - 5:01:14 PM - Greg Robidoux | Read The Tip |
|
Great glad you got this to work. Any thanks for teaching me a little spanish. :) |
|
| 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
|
|
| 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, February 15, 2013 - 10:10:57 AM - Tom B | Read The Tip |
|
Greg, Thanks for the over view. It helped nicely. Tom B |
|
| 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 |
|
| 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 |
|
| 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. |
|
| 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 |
|
| 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 |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |