EXCLE无法导出65535条数据时报错；插入数据代码；

来源：互联网发布：淘宝出售假冒商品2次编辑：程序博客网时间：2024/05/02 01:42

ALTER PROCEDURE loopadd
AS
DECLARE @start INT ;
DECLARE @ends INT ;
DECLARE @shangpinjh NVARCHAR(20);
SET @start = 1 ;
SET @ends = 70000 ;
WHILE @start <= @ends
BEGIN
SET @start = @start + 1
SET @shangpinjh = 'JH'+CAST((3000079+@start) AS nvarchar(7))
--向商品订单表中插入一条数据

--------开始插入 ----从78开始；
INSERT INTO SPJHD
( YS ,
JZ ,
JS ,
DJBH ,
RQ ,
YDJH ,
QDDM ,
DM1 ,
DM2 ,
BYZD1 ,
BYZD5 ,
BYZD12 ,
YGDM ,
DM4 ,
FPLX ,
SL ,
JE ,
BZJE ,
LXDJ ,
BYZD3 ,
BYZD13 ,
LL ,
ZDR ,
RQ_4 ,
BYZD4 ,
RQ_3 ,
YSR ,
YSRQ ,
JZR ,
JZRQ ,
BYZD6 ,
BYZD9 ,
BYZD10 ,
BYZD14 ,
JE_3 ,
JE_2 ,
ZSR ,
LLR ,
BYZD8 ,
BZ
)
VALUES ( '0' ,
'0' ,
'0' ,
@shangpinjh ,
GETDATE() ,
'' ,
'000' ,
'000' ,
'000' ,
'0' ,
'0' ,
1.000000000000000e+000 ,
'000' ,
'000' ,
'0' ,
1.000000000000000e+002 ,
0.000000000000000e+000 ,
0.000000000000000e+000 ,
'' ,
'' ,
5.000000000000000e+000 ,
'1' ,
'裴晓斌' ,
GETDATE() ,
'' ,
NULL ,
'' ,
NULL ,
'' ,
NULL ,
'0' ,
0.000000000000000e+000 ,
0.000000000000000e+000 ,
NULL ,
0.000000000000000e+000 ,
2.268000000000000e+004 ,
'000' ,
'101' ,
0.000000000000000e+000 ,
''
) ;

----------------------------------------------
INSERT INTO SPJHDMX
( HH ,
DJBH ,
MXBH ,
SPDM ,
GG1DM ,
GG2DM ,
SL ,
SL_1 ,
BYZD12 ,
CKJ ,
ZK ,
DJ ,
JE ,
BZJE ,
BZ
)
VALUES ( 1.000000000000000e+000 ,
@shangpinjh ,
'1' ,
'--ABC' ,
'001' ,
'001' ,
1.000000000000000e+002 ,
0.000000000000000e+000 ,
0.000000000000000e+000 ,
0.000000000000000e+000 ,
1.000000000000000e+000 ,
1.200000000000000e+000 ,
1.200000000000000e+002 ,
0.000000000000000e+000 ,
''
)
-----------------------------------
UPDATE SPJHD
SET SL = ISNULL(( SELECT SUM(ISNULL(SL, 0))
FROM SPJHDMX
WHERE DJBH = @shangpinjh
), 0) ,
JE = ISNULL(( SELECT SUM(ISNULL(JE, 0))
FROM SPJHDMX
WHERE DJBH = @shangpinjh
), 0) ,
BZJE = ISNULL(( SELECT SUM(ISNULL(BZJE, 0))
FROM SPJHDMX
WHERE DJBH = @shangpinjh
), 0)
WHERE DJBH = @shangpinjh

---插入结束

END
GO

EXEC loopadd;

---删除数据

DELETE FROM dbo.SPJHDMX WHERE DJBH>='JH3000078';
DELETE FROM dbo.SPJHD WHERE DJBH>='JH3000078';
SELECT * FROM spjhd;

0 0