--start the xp_cmdshell service
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC
master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE--create the testing table
create database bcp_testing
gouse bcp_testing
create table original_table
(
Data bigint
)
declare @i bigint
set @i = 1
while @i < 10000
begin
insert into original_table
select @i
set @i = @i + 1
end select * from original_tabledeclare @iend bigint
declare @SQLStr varchar(8000)
set @iend = 1000
select @SQLStr = ''' bcp "'
+ ' SELECT Data '
+ ' FROM bcp_testing.dbo.original_table '
+ ' WHERE Data >= '
+ '''''' + cast(@iend as varchar(100))+''''''
+ '" QUERYOUT "C:\tmpContentTable.dat" -c -t "||" -T'''
select @SQLStr
EXEC master..xp_cmdshell @SQLStrselect * into new_table from original_table where 1 = 0
SET @SQLStr = ''' BCP "bcp_testing.dbo.new_table" IN "C:\tmpContentTable.dat" -c -t "||" -S"
-T '''
select @SQLStr
EXEC master..xp_cmdshell @SQLStrselect * from new_table
为什么总是得到
''' is not recognized as an internal or external command,
operable program or batch file.
的错误呢?
然后再倒入到另一个table,
关键是数据量比较大,想测试一下用bcp这样导出再导入会不会速度更快一点
gouse bcp_testing
create table original_table
(
Data bigint
)
declare @i bigint
set @i = 1
while @i < 10000
begin
insert into original_table
select @i
set @i = @i + 1
end select * from original_tabledeclare @iend bigint
declare @SQLStr varchar(8000)
set @iend = 1000
select @SQLStr = ' bcp "'
+ ' SELECT Data '
+ ' FROM bcp_testing.dbo.original_table '
+ ' WHERE Data >= '
+ cast(@iend as varchar(100))
+ '" QUERYOUT "C:\tmpContentTable.dat" -c -t "||" -T'
select @SQLStr
EXEC master..xp_cmdshell @SQLStrselect * into new_table from original_table where 1 = 0
SET @SQLStr = ' BCP "bcp_testing.dbo.new_table" IN "C:\tmpContentTable.dat" -c -t "||" -T '
select @SQLStr
EXEC master..xp_cmdshell @SQLStrselect * from new_table
' bcp " SELECT Data FROM bcp_testing.dbo.original_table WHERE Data >= ''1000''" QUERYOUT "C:\tmpContentTable.dat" -c -t "||" -T'这个是bcp的命令,如果去除掉''之后在命令行是可以正常运行的
所以怀疑是不是EXEC master..xp_cmdshell @SQLStr 外部传递参数的时候有限制
如果中间有空白字符怎么处理呢,没有了外部的‘ ’系统会报错的
+ ' SELECT Data '
+ ' FROM bcp_testing.dbo.original_table '
+ ' WHERE Data >= '
+ '''''' + cast(@iend as varchar(100))+''''''
+ '" QUERYOUT "C:\tmpContentTable.dat" -c -t "||" -T'''
select @SQLStr
exec( @SQLStr )
这样会不会可以保留到中间的’‘了
-->
+ '''' + cast(@iend as varchar(100))+''''