Methods of Importing Data into SQL Server Database

This is a brief of options you can use to import/export data into/from SQL Server database. And These are the methods shipped with SQL Server.

1. Export/Import Data Wizard
2. SSIS (can by created from 1)
3. BCP

Can be invokded by xp_cmdshell if it has to be used in SQL script.

Here is its syntax and usage: http://msdn.microsoft.com/en-us/library/ms162802.aspx

--generate format file(non-xml or xml format file.)
--e.g.non-xml

bcp dbs.dbolocation format nul -T -c  -f Currency.fmt

Trick: if you are using query and need to create format file for it, you can either
generating one from table the first and then manually modify it, or you can create a view in database and then generate format file from the using the view.
--export
--import using format file

4. BULK INSERT

Another version of BCP. It is a T-SQL Command, Can be used directly in SQL script.

BULK INSERT dbo.location
FROM 'C:\locationData.txt'
WITH ( FIELDTERMINATOR =',', ROWTERMINATOR = '\n' )

5. OPENROWSET

It's a T-SQL command. It can be used to import data from various data sources, such as spreadsheet, etc.

It's often used with INSERT INTO statement.

INSERT INTO dbo.Location
SELECT * FROM OPENROWSET('csv drive','csvfile.txt')

I have an example to load XML file into database for further transformation.

create table #tmpXML (xml315 XML);
EXEC('INSERT INTO #tmpXML(xml315)
 SELECT CAST(xContent AS XML)
 FROM OPENROWSET(
   BULK '''+@XMLFilePath+''',
   SINGLE_BLOB) AS xfile(xContent)'
)
INSERT INTO [dbo].[ShipmentStatusMessage]
      ([MessageSender]
      ,[MessageRecipient]
      ,[MessageDate]
      ,[MessageTime]
      ,[MessageID]
      ,[Filename]
      ,[X-12]
      ,[VesselName]
      ,[VesselCode]
      ,[VesselCodeQualifier]
      ,[VoyageNumber]
      ,[OperatingCarrier])
 SELECT
 r.value('./MessageSender[1]', 'NVARCHAR(15)'),
 r.value('./MessageRecipient[1]', 'NVARCHAR(15)'),
 r.value('./MessageDate[1]', 'NVARCHAR(8)'),
 r.value('./MessageTime[1]', 'NVARCHAR(8)'),
 r.value('./MessageID[1]', 'NVARCHAR(20)'),
 r.value('./Filename[1]', 'NVARCHAR(50)'),
 r.value('./X-12[1]', 'NVARCHAR(2000)'),
 r.query('./ShipmentStatus/VesselInformation/VesselName').value('.', 'NVARCHAR(28)'),
 r.query('ShipmentStatus/VesselInformation/VesselCode').value('.', 'NVARCHAR(8)'),
 r.query('ShipmentStatus/VesselInformation/VesselCodeQualifier').value('.', 'NVARCHAR(1)'),
 r.query('ShipmentStatus/VesselInformation/VoyageNumber').value('.', 'NVARCHAR(10)'),
 r.query('ShipmentStatus/VesselInformation/OperatingCarrier').value('.', 'NVARCHAR(10)')
 from #tmpXML
 cross apply xml315.nodes('//ShipmentStatusMessage') as T(r)

6. OPENDATASOURCE
Similar to OPENROWSET. I see people treat it as a table and do insert, update and delete on it.

7. OPENQUERY

Another T-SQL Command you can use directly in SQL Server. To use it, linked server
will have to be created the first. A linked server is a static data source comparing to the ones created on the fly in the opendatasource or openrowset.

8. LINKED Server

You can issue query against linked server straight forward.

Assuming you have created a linked server to another SQL Server database and you called it abc123, in OPENQUERY, you can use it this way:

select * from OPENQUERY(abc123,''select * from aTable)

Or, you can query linked server directly like this:

select * from abc123..aTable

openrowset,

openrowset
Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

opendatasource

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.

SELECT   * FROM      OPENDATASOURCE(          'SQLOLEDB',          'Data Source=ServerName;User ID=MyUID;Password=MyPass'          ).Northwind.dbo.Categories

openquery
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

EXEC sp_addlinkedserver 'OracleSvr',     'Oracle 7.3',     'MSDAORA',     'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')  GO