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