sqlserver使用bcp分解字符串

这个问题来自论坛提问：

---

---

可以使用bcp将原数据导出到临时文件，然后在用bcp导入到另外一个表中 测试SQL

---

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

 

*/

 

---

转载于:https://www.cnblogs.com/cl1024cl/archive/2009/01/12/6204860.html