By: Greg Robidoux | 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.
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
About the author
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
