sqlserver使用bcp分解字符串

create database jinjazz_test

go

use jinjazz_test

go

--建立测试环境

set nocount on

create table test(日期varchar(20),参数varchar(200))

insert into test select '2008-10-1','0000004741|1000400821|81100390861|100016179|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004742|1000400822|81100390862|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004743|1000400823|81100390863|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004744|1000400824|81100390865|100016174|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004745|1000400825|81100390866|100016173|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004746|1000400826|81100390867|100016172|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

insert into test select '2008-10-1','0000004747|1000400827|81100390868|100016171|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'

go

create table test1(登记号varchar(20),处方号varchar(20),单号varchar(20),流水号varchar(20),项目名称varchar(20),单价varchar(20),数量varchar(20),人员varchar(20),经办人varchar(20),单位varchar(20),规格varchar(20))

go

?

--测试

EXEC master..xp_cmdshell 'bcp "select left(参数,len(参数)-1) from jinjazz_test.dbo.test" queryout? e:/DT.txt -c -S10.1.3.252 -Usa -Psqltest'

EXEC master..xp_cmdshell 'bcp "jinjazz_test.dbo.test1" in e:/DT.txt -c -S10.1.3.252 -Usa -Psqltest -t"|"'

?

select * from test1

?

go

use master

go

drop database jinjazz_test

/*

登记号???????????????? 处方号???????????????? 单号????????????????? 流水号???????????????? 项目名称??????????????? 单价????????????????? 数量????????????????? 人员????????????????? 经办人???????????????? 单位????????????????? 规格

-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

0000004741?????????? 1000400821?????????? 81100390861????????? 100016179??????????? 0.9%氯化钠注射液????????? 5.3000????????? ?????1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶?????????????????? 500ml

0000004742?????????? 1000400822?????????? 81100390862????????? 100016175??????????? 0.9%氯化钠注射液????????? 5.3000?????????????? 1.0????????????????? 陶先明????????????? ???-1?????????????????? 瓶?????????????????? 500ml

0000004743?????????? 1000400823?????????? 81100390863????????? 100016175??????????? 0.9%氯化钠注射液????????? 5.3000?????????????? 1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶????????????????? ?500ml

0000004744?????????? 1000400824?????????? 81100390865????????? 100016174??????????? 0.9%氯化钠注射液????????? 5.3000?????????????? 1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶?????????????????? 500ml

0000004745?????????? 1000400825???? ??????81100390866????????? 100016173??????????? 0.9%氯化钠注射液????????? 5.3000?????????????? 1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶?????????????????? 500ml

0000004746?????????? 1000400826?????????? 81100390867????????? 100016172?????? ?????0.9%氯化钠注射液????????? 5.3000?????????????? 1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶?????????????????? 500ml

0000004747?????????? 1000400827?????????? 81100390868????????? 100016171??????????? 0.9%氯化钠注射液????????? 5.3000??????????? ???1.0????????????????? 陶先明???????????????? -1?????????????????? 瓶?????????????????? 500ml

?

*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!