字段都一样吗?create table TB(col1 varchar(50),col2 varchar(50)......) declare @iSQL varchar(8000) set @iSQL='' Set @iSQL='bcp "DBname.dbo.TB" In "D:\filename.TXT" -c -S"192.0.0.16" -U"sa" -P"pwd"' EXEC master..xp_cmdshell @iSQLselect * from TB如果文件名有规则,写个循环就OK了
如果想用前台程序,那么读取文件 到dateset,再bulkcopy到数据库也行。
exec master..xp_cmdshell 'Bcp 库名.dbo.表名 in c:\目录名\文本名.txt -S 127.0.0.1 -U sa -P 密码 -t \t -c'
给你写了个循环,之间执行就可以了。 create table bcpintxt(col varchar(200)) create table datatable(col varchar(200)) --创建存放数据的表,根据自己的字段数据可以再添加declare @SQL varchar(8000),@file varchar(8000) insert into bcpintxt(col) exec master..xp_cmdshell 'dir c:\test\*.txt /b' --注意这里修改存放文件的目录 delete from bcpintxt where col is null declare fetch_id cursor for select col from bcpintxt order by col open fetch_id fetch fetch_id into @file while @@fetch_status=0 begin Set @SQL='Bcp db_test.dbo.datatable in c:\test\'+@file+' -S 127.0.0.1 -U sa -P 123 -t \t -c' print @sql EXEC master..xp_cmdshell @sql fetch next from fetch_id into @file end close fetch_id deallocate fetch_iddrop table bcpintxt
加个注释 create table bcpintxt(col varchar(200)) create table datatable(col varchar(200)) --创建存放数据的表,根据自己的字段数据可以再添加declare @SQL varchar(8000),@file varchar(8000) insert into bcpintxt(col) exec master..xp_cmdshell 'dir c:\test\*.txt /b' --注意这里修改存放文件的目录 delete from bcpintxt where col is null declare fetch_id cursor for select col from bcpintxt order by col open fetch_id fetch fetch_id into @file while @@fetch_status=0 begin Set @SQL='Bcp 数据库名.dbo.datatable in c:\test\'+@file+' -S 127.0.0.1 -U sa -P sa的密码 -t \t -c' --注意这里修改数据库名称,用户名,密码,和存放文件的目录 print @sql EXEC master..xp_cmdshell @sql fetch next from fetch_id into @file end close fetch_id deallocate fetch_iddrop table bcpintxt
declare @iSQL varchar(8000)
set @iSQL=''
Set @iSQL='bcp "DBname.dbo.TB" In "D:\filename.TXT" -c -S"192.0.0.16" -U"sa" -P"pwd"'
EXEC master..xp_cmdshell @iSQLselect * from TB如果文件名有规则,写个循环就OK了
create table bcpintxt(col varchar(200))
create table datatable(col varchar(200)) --创建存放数据的表,根据自己的字段数据可以再添加declare @SQL varchar(8000),@file varchar(8000)
insert into bcpintxt(col) exec master..xp_cmdshell 'dir c:\test\*.txt /b' --注意这里修改存放文件的目录
delete from bcpintxt where col is null
declare fetch_id cursor for select col from bcpintxt order by col
open fetch_id
fetch fetch_id into @file
while @@fetch_status=0
begin
Set @SQL='Bcp db_test.dbo.datatable in c:\test\'+@file+' -S 127.0.0.1 -U sa -P 123 -t \t -c'
print @sql
EXEC master..xp_cmdshell @sql
fetch next from fetch_id into @file
end
close fetch_id
deallocate fetch_iddrop table bcpintxt
create table bcpintxt(col varchar(200))
create table datatable(col varchar(200)) --创建存放数据的表,根据自己的字段数据可以再添加declare @SQL varchar(8000),@file varchar(8000)
insert into bcpintxt(col) exec master..xp_cmdshell 'dir c:\test\*.txt /b' --注意这里修改存放文件的目录
delete from bcpintxt where col is null
declare fetch_id cursor for select col from bcpintxt order by col
open fetch_id
fetch fetch_id into @file
while @@fetch_status=0
begin
Set @SQL='Bcp 数据库名.dbo.datatable in c:\test\'+@file+' -S 127.0.0.1 -U sa -P sa的密码 -t \t -c' --注意这里修改数据库名称,用户名,密码,和存放文件的目录
print @sql
EXEC master..xp_cmdshell @sql
fetch next from fetch_id into @file
end
close fetch_id
deallocate fetch_iddrop table bcpintxt