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.
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.
bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName
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$]')
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.
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.
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 ?
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
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.
Tuesday, April 16, 2013 - 10:26:20 AM - Greg Robidoux
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
Sunday, March 10, 2013 - 11:35:49 AM - Jose Lourenco
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?
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
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".