SQL Insert Case When Update

SQL批量插入与更新示例

最新推荐文章于 2022-09-14 14:07:12 发布

转载最新推荐文章于 2022-09-14 14:07:12 发布 · 126 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/qianjinyan/p/9181803.html

本文介绍了一个SQL脚本示例，展示了如何创建表格、批量插入数据，并更新特定字段。通过使用动态设置变量的方式，实现了高效的数据填充及性别与备注字段的更新。

CREATE TABLE LoadTestTable 
( 
    ID INT IDENTITY(1,1), 
    FIRSTNAME VARCHAR(50), 
	LASTNAME VARCHAR(50), 
    GENDER INT,
    MOBILE BIGINT,
    IDnum VARCHAR(50),
    singautre VARCHAR(50),
    OPENID VARCHAR(50),
    UNIONID VARCHAR(50),
    MEMO  VARCHAR(50)
);



DECLARE @NAME VARCHAR(50)
DECLARE @NAME2 VARCHAR(50)
DECLARE @GENDER INT
DECLARE @HEIGHT INT
DECLARE @MOBILE BIGINT
DECLARE @IDNUM VARCHAR(50)
DECLARE @OPENID VARCHAR(50)
DECLARE @UNIONID VARCHAR(50)
DECLARE @SINGAUTRE VARCHAR(50)
DECLARE @VAR INT
SET @VAR=1000
SET @MOBILE = 21111173440
WHILE @VAR<10000
BEGIN
 
 
SET @NAME = 'firstname' +CONVERT(VARCHAR(50),@VAR);
SET @NAME2 = 'lastname' +CONVERT(VARCHAR(50),@VAR);
SET @GENDER=1;
SET @HEIGHT=160;
SET @MOBILE = @MOBILE+1;
SET @IDNUM = 'ABCD12341QAZ12' + CONVERT(VARCHAR(50),@VAR);-----18位身份证
SET @OPENID = 'OPENID1111_'+ CONVERT(VARCHAR(50),@VAR);
SET @UNIONID = 'UNIONID1111_'+ CONVERT(VARCHAR(50),@VAR);
 
INSERT LOADTESTTABLE(FIRSTNAME,LASTNAME,GENDER,MOBILE,IDNUM,SINGAUTRE,OPENID,UNIONID)
VALUES(@NAME,@NAME2,@GENDER,@MOBILE,@IDNUM,NULL,@OPENID,@UNIONID)
 
SET @VAR=@VAR+1
END　　



SELECT * FROM LOADTESTTABLE


update LOADTESTTABLE
set GENDER =
case when
(id %2 =0) then '1'
else '2'end


update LOADTESTTABLE
set memo =
case when
(gender=1) then 'F'
else 'M'end




SELECT * FROM [V_CUSTOMERSUMMARY] where ValidPoints>0  ---54秒

转载于:https://www.cnblogs.com/qianjinyan/p/9181803.html