22,209
社区成员
发帖
与我相关
我的任务
分享
-----fmt 文件内容
--9.0
--4
--1 SQLCHAR 0 20 " " 1 cardId ""
--2 SQLCHAR 0 20 " " 2 name ""
--3 SQLCHAR 0 20 " " 3 startDate ""
--4 SQLCHAR 0 20 "\n" 4 endDate ""
-----txt内容
--cardId name startDate endDate
--1 lily 2007-01-20 2007-02-11
--2 hanmei 2008-01-21 2007-03-12
--3 lilei 2009-01-22 2009-02-11
insert tb_name(指定列)
SELECT cardId, name, startDate ,endDate
FROM OPENROWSET(BULK 'd:\test.txt',
FORMATFILE='d:\test.Fmt',
firstrow=2
) as t1
/** 导入文本文件 */
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
/** 导出文本文件 */
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
/**导出到TXT文本,用逗号分开*/
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)