| |

VerySource

 Forgot password?
 Register
Search
View: 1203|Reply: 7

Waiting for your reply online, how to write a bcp statement whose directory name is a variable?

[Copy link]

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-1-16 15:00:01
| Show all posts |Read mode
declare @user varchar (100)
declare @pw varchar (100)
set @user = 'sa'
set @pw = '16898758'
declare @qushi varchar (20)
declare @sheng varchar (20)
declare @sql varchar (1000)
declare cur_qushi scroll cursor for select distinct District from cell phone segment mobile segment for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@ fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left (Col001, 7) in (select number segment from mobile.dbo. mobile segment mobile segment where city ==' '+ @qushi +' '' ) '
exec outsheng @ qushi1 = @ qushi, @ sheng1 = @ sheng output
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\' + @ sheng + '\' + @qushi + '.txt -c -U' + @user + '-P' + @pw
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi


Please correct me, my error is: SQLState = S1000, NativeError = 0
Error = [Microsoft] [ODBC SQL Server Driver] could not open the BCP master data file
NULL

My mobile phone table table format is:
Area code Province city
0510 Jiangsu Wuxi 1340000
0510 Jiangsu Wuxi 1340001
0510 Jiangsu Wuxi 1340002
0510 Jiangsu Wuxi 1340003
0510 Jiangsu Wuxi 1340004
025 Nanjing, Jiangsu 1340005
025 Nanjing, Jiangsu 1340006
025 Nanjing, Jiangsu 1340007
0511 Zhenjiang, Jiangsu 1340008
The format of my blacklist table is:
Col001
13709499546
13999045904
13566036035
13608334588
13575856588
13883743004
13977378668
Reply

Use magic Report

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-1-20 20:45:01
| Show all posts
My outsheng stored procedure code is as follows:

USE mobile
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'outsheng' AND type = 'p')
   DROP procedure outsheng
go
create procedure outsheng
@ qushi1 varchar (20),
@ sheng1 varchar (20) output
as
select @ sheng1 = Province from mobile.dbo. mobile segment mobile segment where city == qq1
return
go
Reply

Use magic Report

0

Threads

3

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-1-21 18:18:02
| Show all posts
Don't know the error
Reply

Use magic Report

0

Threads

30

Posts

25.00

Credits

Newbie

Rank: 1

Credits
25.00

 China

Post time: 2020-1-26 10:18:01
| Show all posts
--nailed it
--BCP cannot help you build directories
declare @user varchar (100)
declare @pw varchar (100)
set @user = 'sa'
set @pw = '16898758'
declare @qushi varchar (20)
declare @sheng varchar (20)
declare @mdsql varchar (1000)
declare @sql varchar (1000)
declare cur_qushi scroll cursor for select distinct Districts from cell phone segment mobile segment for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@ fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left (Col001, 7) in (select number segment from mobile.dbo. mobile segment where mobile segment where district city =' '' + @qushi + '' ' ) '
exec outsheng @ qushi1 = @ qushi, @ sheng1 = @ sheng output
set @mdsql = 'if not exist D:\numbers\' + @ sheng + 'md D:\numbers\' + @ sheng
--print (@mdsql)
exec master..xp_cmdshell @mdsql
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\' + @ sheng + '\' + @qushi + '.txt -c -U' + @user + '-P' + @pw
--print (@sql)
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-1-26 11:00:02
| Show all posts
learned.
Reply

Use magic Report

0

Threads

30

Posts

25.00

Credits

Newbie

Rank: 1

Credits
25.00

 China

Post time: 2020-1-26 19:36:01
| Show all posts
-Assuming directory D:\numbers already exists
-Establish test data
if object_id ('Mobile segment mobile segment') is not null drop table Mobile segment mobile segment
create table Mobile segment mobile segments
insert Mobile Talk Mobile Talk
select '0510', 'Jiangsu', 'Wuxi', '1340000' union all
select '0510', 'Jiangsu', 'Wuxi', '1340001'union all
select '0510', 'Jiangsu', 'Wuxi', '1340002'union all
select '0510', 'Jiangsu', 'Wuxi', '1340003'union all
select '0510', 'Jiangsu', 'Wuxi', '1340004'union all
select '025', 'Jiangsu', 'Nanjing', '1340005'union all
select '025', 'Jiangsu', 'Nanjing', '1340006'union all
select '025', 'Jiangsu', 'Nanjing', '1340007'union all
select '0511', 'Jiangsu', 'Zhenjiang', '1340008'union all
select '020', 'Guangdong', 'Guangzhou', '1300008'
go

if object_id ('blacklist') is not null drop table blacklist
create table blacklist (Col001 varchar (100))
insert blacklist
select '13400009546' union all
select '13999045904' union all
select '13566036035' union all
select '13608334588' union all
select '13575856588' union all
select '13883743004' union all
select '13977378668' union all
select '13000088668'
go

-Establish a stored procedure
USE mobile
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'outsheng' AND type = 'p')
   DROP procedure outsheng
go
create procedure outsheng
@ qushi1 varchar (20),
@ sheng1 varchar (20) output
as
select @ sheng1 = Province from mobile.dbo. mobile segment mobile segment where city == qq1
return
go

--Write cursor
declare @user varchar (100)
declare @pw varchar (100)
set @user = 'sa'
set @pw = 'ggs'
declare @qushi varchar (20)
declare @sheng varchar (20)
declare @mdsql varchar (1000)
declare @sql varchar (1000)
declare cur_qushi scroll cursor for select distinct Districts from cell phone segment mobile segment for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@ fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left (Col001, 7) in (select number segment from mobile.dbo. mobile segment where mobile segment where district city =' '' + @qushi + '' ' ) '
exec outsheng @ qushi1 = @ qushi, @ sheng1 = @ sheng output
set @mdsql = 'if not exist D:\numbers\' + @ sheng + 'md D:\numbers\' + @ sheng
--print (@mdsql)
exec master..xp_cmdshell @mdsql
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\' + @ sheng + '\' + @qushi + '.txt -c -U' + @user + '-P' + @pw
--print (@sql)
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi

--View Results
--Go to the D:\numbers directory, you can see the two directories "Jiangsu" and "Guangdong"
-There are two files in the "Jiangsu" directory: Nanjing.txt, Wuxi.txt (Contents: 14000095546)
-There is a file in the "Jiangsu" directory: Guangdong.txt (content: 13000088668)
Reply

Use magic Report

0

Threads

7

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

Post time: 2020-1-28 11:54:01
| Show all posts
Path quoted:
set @sql = 'bcp "' + @sql + '" queryout "D:\numbers\' + @ sheng + '\' + @qushi + '.txt" -c -U' + @user + '-P' + @pw
Reply

Use magic Report

1

Threads

11

Posts

8.00

Credits

Newbie

Rank: 1

Credits
8.00

 United States

Post time: 2020-2-23 12:30:01
| Show all posts
exec xp_cmdshell 'bcp "select * from recodata.dbo. chapter table"
 queryout "c:\11.txt" -c -Usa -Sserver -P, '
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list