- Data Transformation Services [DTS]
- SQL Server Integration Services [SSIS]
- Bulk Copy [BCP]
Rest of above GUI operation there is another option available via the T-SQL language is the OPENROWSET command.This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
'provider_string' }
, { [ catalog. ] [ schema. ] object
'query'
}
BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [
SINGLE_BLOB SINGLE_CLOB SINGLE_NCLOB }
} )
[ , CODEPAGE = { 'ACP' 'OEM' 'RAW' 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
With the following example you can write a simple job for the operation:-
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO
