Many new functions have been added to SQL Server 2005. Among them, the bulk function in the OPENROWSET function can batch import data in file types into the database. According to MSDN:
BULK is a new rowset provider specified in the OPENROWSET function that allows you to access file data in relational format. To retrieve data from a file, you specify the BULK option, a file name, and a format file created with bcp.exe or manually. You can specify the name of the result column in parentheses after the alias of the table returned from OPENROWSET. The following is an example to briefly explain
if there is a TXT file, as follows
Asia.txt
1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong
And we create a data table called region
CREATE TABLE REGION
(ID INT,
REGION VARCHAR(25),
DATA VARCHAR(MAX)
)
We can use the bulk function to add data to the region table.
INSERT INTO REGION (ID, REGION, DATA)
SELECT 1 AS ID, 'ASIA' AS REGION,
* FROM OPENROWSET( BULK 'C:DATAASIA.TXT',SINGLE_CLOB)
AS MYTABLE
, the data field in the region table is the content in the asia.txt text file.
Source: jackyrong BLOG