In this Article We are going to see
the Different methods for Importing Excel data to SQL server or Data to SQL Server.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
|
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.
BULK INSERT 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.
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.
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.
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.
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.
No comments:
Post a Comment