T-SQL查询
wangzhpwang
<--Wechat
展开
-
sql server unique index 开启 ignore_dup_key 去除重复数据
/************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.1.35 * Time: 2014/7/12 12:53:07 ********************************************************原创 2014-07-12 13:10:37 · 2333 阅读 · 1 评论 -
sql server 小数据插入,根据表名生成insert 语句,求加固
sql server 小数据插入,根据表名生成insert 语句,求加固IF OBJECT_ID('f_xx')IS NOT NULLDROP FUNCTION DBO.f_xxGO--创建方法,返回sqlCREATE FUNCTION f_xx(@TNAME VARCHAR(111))RETURNS NVARCHAR(MAX)ASBEGIN DECLARE @COLST原创 2014-07-12 13:51:22 · 631 阅读 · 0 评论 -
Tsql生成序列、日期序列
--方法1SET NOCOUNT ONUSE tempdbGOIF OBJECT_ID('dbo.nums')IS NOT NULLDROP TABLE dbo.nums;GOCREATE TABLE dbo.nums(n INT NOT NULL PRIMARY KEY);DECLARE @max AS INT,@rc AS INTSET @max=1000000SET @r翻译 2014-07-30 12:47:07 · 585 阅读 · 0 评论 -
IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULL
IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULLBEGIN PRINT 'Dropping procedure UP_GET_XXX_SEQNO' DROP PROCEDURE DBO.[UP_GET_XXX_SEQNO] IF @@ERROR = 0 PRINT 'Procedure UP_GET_XXX_SE原创 2014-08-08 15:12:22 · 937 阅读 · 0 评论 -
TSQL 带参数的游标 表值类型+record + cursor 模仿 plsql %rowtype
SET NOCOUNT ON;IF EXISTS( SELECT 1 FROM SYS.types AS t WHERE t.name = 'a01_A01_NODE_R_TABLE_TYPE' ) DROP TYPE dbo.a01_A01_NODE_R_TABLE_TYPEGOCREATE TYPE dbo.a01_A01_N原创 2014-08-10 21:30:48 · 756 阅读 · 0 评论 -
sql server 2012 over()窗口的功能扩充,连接聚合示例
sql server 2012 over()窗口的功能扩充,连接聚合示例原创 2014-09-03 15:00:25 · 1003 阅读 · 0 评论 -
PLSQL TO TSQL , RECORE ,%TYPE,%ROWTYPE
SET NOCOUNT ONGOIF OBJECT_ID('TT') IS NOT NULL DROP TABLE ttGOCREATE TABLE TT( f1 NVARCHAR(100) DEFAULT 'VAL', f2 NVARCHAR(100) DEFAULT 'VAL', f3 NVARCHAR(100) DEFAULT 'VAL',原创 2014-08-08 15:06:10 · 643 阅读 · 0 评论 -
Not In 不等号对比, 用exits代替in效果更好
Not in 会转化成一系列的 and case 1SELECT @results = count(filterCriterion_sv) FROM tbl_IN_VS_AND WHERE filterCriterion_sv NOT IN (214, 215, 216, 217)case 2SELECT @results = count(filterCriterion_sv)翻译 2015-01-16 15:33:14 · 1141 阅读 · 0 评论 -
自动生成unpivot,方便table展示字符度量
DECLARE @TBLNAME NVARCHAR(100),@SCHEMA NVARCHAR(100) --SELECT @TBLNAME='T_ORGAN_OUTLET_WEEKLY_REPORT',@SCHEMA='DBO'CREATE PROC up_auto_create_unpivot( @TBLNAME NVARCHAR(100),@SCHEMA NVARCHAR(100))原创 2017-11-28 13:39:02 · 341 阅读 · 0 评论