1 打开“SQL Server Management Studio”管理工具，创建一张测试表：CREATE TABLE tblCaseWhen( Id varchar(40) NOT NULL, Col1 varchar(50) NULL, Col2 int NULL, Col3 varchar(50) NULL, Col4 varchar(50) NULL)2 往测试表中插入测试数据：insert into tblCaseWhen(Id, Col1, Col2) values(&...
1、编写sql server生成insert 语句的存储过程: USE[SHMAP]GO/*** Object: StoredProcedure [dbo].[proc_insert] Script Date: 2016/4/13 15:33:41 ***/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOALTERproc[dbo].[proc_insert](@tablenamevarchar(256))asbeginsetnocountondeclare@sqlstrvarchar(8000)declare...
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''+'+'replace('+a.name+',''',''')' + '+'''+' end' when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end' when...
create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1) insert into testklup select 1,1,100,'aaa' insert into testklup select 2,2,200,'bbb' insert into testklup select 3,3,300,'ccc' 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 开启会话1 模拟高频u...
CASCADE | CASE | CHECK | CHECKPOINT | CLOSE | CLUSTERED | COALESCE COLLATE | COLUMN | COMMIT | COMPUTE | CONSTRAINT | CONTAINS CONTAINSTABLE | CONTINUE | CONVERT | CREATE | CROSS | CURRENT CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_USER ...
2:可以用取到的或傳入的參數進行判斷,來進行update,insert,delete 等等操作 eg: IF @strUNIT_CODE=’’ BEGIN //具體的操作 End Else Begin //具體的操作 End 3:有關游標的問題 Eg: declare db cursor for //聲明一個游標(db為其名稱) SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@str...
DML和DQL 增删改查 SELECT * FROM grade --新增 insert -- 向年级表中新增3条数据 INSERT INTO grade(gradeID,gradeName) VALUES(4,'4年级'); INSERT INTO grade(gradeID,gradeName) VALUES(5,'5年级'); INSERT INTO grade(gradeID,gradeName) VALUES(6,'6年级'); -1.备份表:create table 新表(不...
第二步，插入数据——INSERT INTO敲起！插入这个事儿呢，说白了就是把东西搬到别处放好。像搬家一样...
在做数据统计的时候,行转列,列转行是经常碰到的问题。case when方式太麻烦了,而且可扩展性不强,可以使用 PIVOT,UNPIVOT比较快速实现行转列,列转行,而且可扩展性强 一、行转列 1、测试数据准备 CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), --学生姓名 ...
INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2) SET @x = @x + 1 END GO CREATE CLUSTERED INDEX cidx ON t1 (c1) CREATE NONCLUSTERED INDEX idx1 ON t1 (c2) GO CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1 ...