Different Options for Importing Data into SQL Server

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 either DTS (SQL 2000) or SSIS (SQL 2005).  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 and/or DTS 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 DTS or 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.