DB2手工导入到MSSQL2000中
第一步：利用bcp生成格式化文件。EXEC master..xp_cmdshell 'BCP sinonet.dbo.table3 format nul -f d:/User_format1.fmt -c -T'
第二步：修改格式化文件，主要是列结束符。
8.0
99
1 SQLCHAR 0 32 "|" 1 SERIALNO Chinese_PRC_CI_AS
2 SQLCHAR 0 32 "|" 2 RELATIVESERIALNO Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "|" 3 OCCURDATE Chinese_PRC_CI_AS
4 SQLCHAR 0 18 "|" 4 APPLYSITE Chinese_PRC_CI_AS
5 SQLCHAR 0 18 "|" 5 BUSICHANTYPE Chinese_PRC_CI_AS
6 SQLCHAR 0 18 "|" 6 PHASETYPE Chinese_PRC_CI_AS
7 SQLCHAR 0 18 "|" 7 APPLYTYPE Chinese_PRC_CI_AS
8 SQLCHAR 0 32 "|" 8 CUSTOMERID Chinese_PRC_CI_AS
9 SQLCHAR 0 80 "|" 9 CUSTOMERNAME Chinese_PRC_CI_AS
10 SQLCHAR 0 18 "|" 10 CERTTYPE Chinese_PRC_CI_AS
11 SQLCHAR 0 32 "|" 11 CERTID Chinese_PRC_CI_AS
12 SQLCHAR 0 32 "|" 12 TELEPHONE Chinese_PRC_CI_AS
13 SQLCHAR 0 32 "|" 13 MOBILETELEPHONE Chinese_PRC_CI_AS
14 SQLCHAR 0 18 "|" 14 COMMZIP Chinese_PRC_CI_AS
15 SQLCHAR 0 18 "|" 15 IFADDMORT Chinese_PRC_CI_AS
16 SQLCHAR 0 18 "|" 16 IFTRANSMORT Chinese_PRC_CI_AS
17 SQLCHAR 0 32 "|" 17 COOPAGRENO Chinese_PRC_CI_AS
18 SQLCHAR 0 80 "|" 18 COOPPROJNAME Chinese_PRC_CI_AS
19 SQLCHAR 0 32 "|" 19 BUILPROJNO Chinese_PRC_CI_AS
20 SQLCHAR 0 80 "|" 20 BUILPROJNAME Chinese_PRC_CI_AS
21 SQLCHAR 0 18 "|" 21 BAILCHARTYPE Chinese_PRC_CI_AS
22 SQLCHAR 0 41 "|" 22 BAILRATIO ""
23 SQLCHAR 0 41 "|" 23 BUSINESSSUM ""
24 SQLCHAR 0 18 "|" 24 BUSINESSCURRENCY Chinese_PRC_CI_AS
25 SQLCHAR 0 41 "|" 25 GUARANTYVALUE ""
26 SQLCHAR 0 18 "|" 26 SUBJECTNO Chinese_PRC_CI_AS
27 SQLCHAR 0 18 "|" 27 DRAWINGTYPE Chinese_PRC_CI_AS
28 SQLCHAR 0 10 "|" 28 CORPU Chinese_PRC_CI_AS
29 SQLCHAR 0 200 "|" 29 COMMADD Chinese_PRC_CI_AS
30 SQLCHAR 0 18 "|" 30 BUSINESSTYPE Chinese_PRC_CI_AS
31 SQLCHAR 0 32 "|" 31 LOANTYPE1 Chinese_PRC_CI_AS
32 SQLCHAR 0 18 "|" 32 VOUCHTYPE Chinese_PRC_CI_AS
33 SQLCHAR 0 40 "|" 33 DOWNPAYMENT Chinese_PRC_CI_AS
34 SQLCHAR 0 41 "|" 34 BUSINESSPROP ""
35 SQLCHAR 0 18 "|" 35 PUNIEXTETYPE Chinese_PRC_CI_AS
36 SQLCHAR 0 41 "|" 36 PUNIEXTE ""
37 SQLCHAR 0 18 "|" 37 IFEXHIB Chinese_PRC_CI_AS
38 SQLCHAR 0 18 "|" 38 IFREDUCE Chinese_PRC_CI_AS
39 SQLCHAR 0 18 "|" 39 IFCOMBLOAN Chinese_PRC_CI_AS
40 SQLCHAR 0 18 "|" 40 IFINSU Chinese_PRC_CI_AS
41 SQLCHAR 0 18 "|" 41 IFADVAPAY Chinese_PRC_CI_AS
42 SQLCHAR 0 32 "|" 42 RATEFLOAT Chinese_PRC_CI_AS
43 SQLCHAR 0 32 "|" 43 BUSINESSRATE Chinese_PRC_CI_AS
44 SQLCHAR 0 18 "|" 44 FINERATETYPE Chinese_PRC_CI_AS
45 SQLCHAR 0 32 "|" 45 FINERATEFLOAT Chinese_PRC_CI_AS
46 SQLCHAR 0 32 "|" 46 FINERATE Chinese_PRC_CI_AS
47 SQLCHAR 0 18 "|" 47 IFDISCOUNT Chinese_PRC_CI_AS
48 SQLCHAR 0 41 "|" 48 DISCOUNTRATIO ""
49 SQLCHAR 0 12 "|" 49 DISCBEGINPHAS ""
50 SQLCHAR 0 12 "|" 50 DISCPHAS ""
51 SQLCHAR 0 20 "|" 51 APPROVEDATE Chinese_PRC_CI_AS
52 SQLCHAR 0 18 "|" 52 APPROVERESULT Chinese_PRC_CI_AS
53 SQLCHAR 0 32 "|" 53 CUSTRATADVICE Chinese_PRC_CI_AS
54 SQLCHAR 0 20 "|" 54 CUSTRATDATE Chinese_PRC_CI_AS
55 SQLCHAR 0 32 "|" 55 FINISHADVICE Chinese_PRC_CI_AS
56 SQLCHAR 0 20 "|" 56 FINISHRATDATE Chinese_PRC_CI_AS
57 SQLCHAR 0 18 "|" 57 TEMPSAVEFLAG Chinese_PRC_CI_AS
58 SQLCHAR 0 200 "|" 58 REMARK Chinese_PRC_CI_AS
59 SQLCHAR 0 32 "|" 59 MANAGEUSERID Chinese_PRC_CI_AS
60 SQLCHAR 0 32 "|" 60 MANAGEORGID Chinese_PRC_CI_AS
61 SQLCHAR 0 32 "|" 61 OPERATEUSERID Chinese_PRC_CI_AS
62 SQLCHAR 0 32 "|" 62 OPERATEORGID Chinese_PRC_CI_AS
63 SQLCHAR 0 32 "|" 63 INPUTUSERID Chinese_PRC_CI_AS
64 SQLCHAR 0 32 "|" 64 INPUTORGID Chinese_PRC_CI_AS
65 SQLCHAR 0 20 "|" 65 INPUTDATE Chinese_PRC_CI_AS
66 SQLCHAR 0 20 "|" 66 UPDATEDATE Chinese_PRC_CI_AS
67 SQLCHAR 0 20 "|" 67 PIGEONHOLEDATE Chinese_PRC_CI_AS
68 SQLCHAR 0 32 "|" 68 OCCURTYPE Chinese_PRC_CI_AS
69 SQLCHAR 0 32 "|" 69 RELATIVEAGREEMENT Chinese_PRC_CI_AS
70 SQLCHAR 0 10 "|" 70 OPERATEDATE Chinese_PRC_CI_AS
71 SQLCHAR 0 18 "|" 71 IFREGCONTRACT Chinese_PRC_CI_AS
72 SQLCHAR 0 18 "|" 72 PURPOSESEL Chinese_PRC_CI_AS
73 SQLCHAR 0 18 "|" 73 PURPOSE Chinese_PRC_CI_AS
74 SQLCHAR 0 10 "|" 74 RATFLAG Chinese_PRC_CI_AS
75 SQLCHAR 0 41 "|" 75 CUSTRATRESULT ""
76 SQLCHAR 0 1000 "|" 76 LASTREASON Chinese_PRC_CI_AS
77 SQLCHAR 0 1000 "|" 77 CUSTRATREASON Chinese_PRC_CI_AS
78 SQLCHAR 0 1000 "|" 78 FINISHRATREASON Chinese_PRC_CI_AS
79 SQLCHAR 0 24 "|" 79 LASTRATRESULT ""
80 SQLCHAR 0 32 "|" 80 LASTADVICE Chinese_PRC_CI_AS
81 SQLCHAR 0 41 "|" 81 FINISHRATRESULT ""
82 SQLCHAR 0 20 "|" 82 LASTRATDATE Chinese_PRC_CI_AS
83 SQLCHAR 0 12 "|" 83 RATCOUNT ""
84 SQLCHAR 0 10 "|" 84 ISRELATIVE Chinese_PRC_CI_AS
85 SQLCHAR 0 20 "|" 85 VOUCHDETAIL Chinese_PRC_CI_AS
86 SQLCHAR 0 18 "|" 86 SPAYMETHOD Chinese_PRC_CI_AS
87 SQLCHAR 0 18 "|" 87 PAYCYC Chinese_PRC_CI_AS
88 SQLCHAR 0 18 "|" 88 CCYC Chinese_PRC_CI_AS
89 SQLCHAR 0 32 "|" 89 CHARDAYCLASS Chinese_PRC_CI_AS
90 SQLCHAR 0 32 "|" 90 DUDECTDATE Chinese_PRC_CI_AS
91 SQLCHAR 0 32 "|" 91 TERMTYPE Chinese_PRC_CI_AS
92 SQLINT 0 4 "|" 92 LOANTERM ""
93 SQLCHAR 0 32 "|" 93 LOANPHAS Chinese_PRC_CI_AS
94 SQLCHAR 0 18 "|" 94 ADJUSTRATETYPE Chinese_PRC_CI_AS
95 SQLCHAR 0 18 "|" 95 RATEADJUSTCYC Chinese_PRC_CI_AS
96 SQLCHAR 0 18 "|" 96 BASERATETYPE Chinese_PRC_CI_AS
97 SQLCHAR 0 32 "|" 97 BASERATE Chinese_PRC_CI_AS
98 SQLCHAR 0 24 "|" 98 RATEFLOATTYPE ""
99 SQLCHAR 0 18 "|\r\n" 99 ICTYPE Chinese_PRC_CI_AS
第三步：利用BULK INSERT导入数据。
BULK INSERT sinonet.dbo.table3
FROM 'd:\resour.txt'
WITH
(
BATCHSIZE=1,--指定批处理中的行数
FORMATFILE='d:\User_format1.fmt',
--FIRSTROW =1,--指定要加载的第一行的行号
--LASTROW=10,--指定要加载的最后一行的行号
--MAXERRORS =10,--指定允许在数据中出现的最大语法错误数，超过该数量后将取消大容量导入操作
--ERRORFILE ='ss'--指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件
--FIELDTERMINATOR ='|'--指定要用于 char 和 widechar 数据文件的字段终止符
)
页:
[1]