有500张文本(.txt结尾的)表,数据格式都是一样的,要把500张表导入到sql server中,怎样做才最快,据说要自己写函数,那位高手能不能帮忙写一下,谢谢了.txt文件是格式都是以道号分隔,格式如:李国辉,38岁,技术员,北京
李大军,39岁,工程师,上海
张卫星,30军,军人,北京
......................文本文件的名字可以是t1,t2.....t500请高手帮忙
李大军,39岁,工程师,上海
张卫星,30军,军人,北京
......................文本文件的名字可以是t1,t2.....t500请高手帮忙
搜一下吧。
(
TID INT PRIMARY KEY,
TCODE VARCHAR(200),
TNAME varchar(100),
PRICE DECIMAL,
TCOUNT INT
)--FIELDTERMINATOR列分隔符 ROWTERMINATOR 行分隔符
BULK INSERT TESTBLUK FROM 'c:\Demo.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
1,DSJ-120,电视机,1865.00,15
2,DSJ-180,电视机,2073.00,10
3,XYJ-13,洗衣机,486.00,20
4,XYJ-20,洗衣机,873.00,12
5,DBX-134,电冰箱,1456.00,8
6,DSJ-340,电视机,3726.00,5
7,WBL-6,微波炉,640.00,10
8,KTQ-12,空调器,2800.00,12
SQL:CREATE TABLE TESTBLUK
(
TID INT PRIMARY KEY,
TCODE VARCHAR(200),
TNAME varchar(100),
PRICE DECIMAL,
TCOUNT INT
)--FIELDTERMINATOR列分隔符 ROWTERMINATOR 行分隔符
BULK INSERT TESTBLUK FROM 'c:\Demo.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
select * from TESTBLUK
GO
DECLARE @I INT
SET @I=1
WHILE 1=1
BEGIN
EXEC ('BULK INSERT test2 FROM ''C:\'+@I+'.TXT'' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'') ')
SET @I=@I+1
IF @I>500 BREAK;
END
USE yourDB;
GO
BULK INSERT yourTable
FROM 'C:\PersonalData.Dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS
);
GO