MSSQL Code

took me some time to search for it eventhough i already found a few months ago ;(

first is to read from excel file..i tried this using Query Analyzer

-- Read from Excel file
SELECT *
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=PATH_TO_EXCEL_FILE_HERE;IMEX=1;HDR=Yes;',
'SELECT * FROM [SHEET_NAME_HERE$]')

Or

SELECT *
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="PATH_TO_EXCEL_FILE_HERE";Extended properties=Excel 8.0;')...SHEET_NAME_HERE$

PS:
[FROM MS]
IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. ,

HDR=Yes means the first row will always be the Header.

second is to save query result to a text file in this example..if it contains alot of records..
using BULK COPY and try this in command windows

BCP "SELECT * FROM DB_NAME..TABLE_NAME" queryout "FILE_NAME_PATH.txt" -S "SERVER_NAME\INSTANCE_NAME" -T -w -k -t"|"

-- insert to a table from a file
BULK INSERT DB_NAME..TABLE_NAME FROM 'FILE_NAME_PATH.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
DATAFILETYPE = 'widechar',
KEEPNULLS
)
GO

see u