高分请教bcp导入数据问题，顶者有分

表File有个path字段保存的数据都是文件路径，每条数据都对应一个txt文件路径
path字段
d:/kdata1.txt
d:/bdata2.txt
d:/cdata3.txt
......

取出File表的path字段的所有数据，并将每条数据当做一个文件路径，
循环使用bcp命令，将txt文本数据导入表Data表

要求写成存储过程，有谁知道怎样个写法？非常感谢！

 

 

用游标做

CREATE PROCEDURE import

AS

declare @filenameandpath varchar(50)
declare @sql varchar(1000)

DECLARE table_cur CURSOR FOR
SELECT path FROM File

OPEN table_cur
FETCH NEXT FROM table_cur INTO @filenameandpath

WHILE @@fetch_status = 0
BEGIN
set @sql='bcp 数据库..Data in '''+@filenameandpath+''' -Sservername -Usa -Ppassword'
EXEC master..xp_cmdshell @sql

FETCH NEXT FROM table_cur INTO @filenameandpath
END
DEALLOCATE table_cur

GO

 

感谢大家！修改了下jacobsan(jacobsan)的代码，测试成功！
CREATE PROCEDURE import
AS
declare @FilePath varchar(50)
declare @sql varchar(1000)

DECLARE table_cur CURSOR FOR
SELECT path FROM Files

OPEN table_cur
FETCH NEXT FROM table_cur INTO @FilePath

WHILE @@fetch_status = 0
BEGIN
set @sql='bcp Test..Data in '+@FilePath+' -S -Usa -P1 -c -t, '
EXEC master..xp_cmdshell @sql

FETCH NEXT FROM table_cur INTO @FilePath
END
DEALLOCATE table_cur
GO