sql常用语句

1.将一张表的结构复制给另外一张新表 SELECT * INTO Z_CustomerExtendedData1 FROM Z_CustomerExtendedData WHERE 1=2

 

2.update ... from

(1) update Z_COLineFullExtendedData  set   Industry1=Z_COLineFullExtendedDataTemp201809282.SICCode
, Industry2=Z_COLineFullExtendedDataTemp201809282.SICSubCode
,  strSubSegmentCodeAP=Z_COLineFullExtendedDataTemp201809282.SubSegmentCodeAP
,  strSICCode=Z_COLineFullExtendedDataTemp201809282.SICCodeGlobal 
,  EndUser=Z_COLineFullExtendedDataTemp201809282.EndCustomerCode
from   Z_CustomerExtendedData,Z_COLineFullExtendedDataTemp201809282  
where   Z_COLineFullExtendedData.CONumber   =   Z_COLineFullExtendedDataTemp201809282.CONumber collate Latin1_General_BIN
and Z_COLineFullExtendedData.COLineNumber   =   Z_COLineFullExtendedDataTemp201809282.OrderLineNumber
collate Latin1_General_BIN

(2)update Z_ItemExtendedData set ExpressItem =ItemExpressTemp.ExpressItem
from ItemExpressTemp
where Z_ItemExtendedData.ItemNumber = ItemExpressTemp.ITEM

 

3.1 查询某个表被哪些存储过程,视图,函数使用到

select name,type_desc from sys.all_sql_modules s
inner join sys.all_objects o on s.object_id=o.object_id
where definition like '%WF_FNExplanation%' order by type_desc,name

3.2 查询某个存储过程被哪些Job调用

SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'%Z_Proc_SendFacilityRemindEmail%'

 

4.将Z_COLineFullExtendedDataTemp20180928中存在,Z_COLineFullExtendedData中不存在的数据,插入COLineFullExtendedDataTemp20180928

INSERT INTO [dbo].[Z_COLineFullExtendedData]
         ([CONumber]
           ,[COLineNumber]
           ,[Industry1]
           ,[Industry2]
           ,[EndUser]         
           ,[strSubSegmentCodeAP]
           ,[strSICCode])    
     select [CONumber]
           ,[OrderLineNumber]
           ,[SICCode]
           ,[SICSubCode]
           ,[EndCustomerCode]         
           ,[SubSegmentCodeAP]
           ,[SICCodeGlobal]
 from Z_COLineFullExtendedDataTemp20180928
 where not exists (select 1 from Z_COLineFullExtendedData
 where Z_COLineFullExtendedData.CONumber = Z_COLineFullExtendedDataTemp20180928.CONumber collate Latin1_General_BIN
 and Z_COLineFullExtendedData.COLineNumber = Z_COLineFullExtendedDataTemp20180928.OrderLineNumber)

 

5.--行转列+原来的行
DECLARE @s NVARCHAR(4000) 
SELECT @s = ISNULL(@s + ',', '') +  QUOTENAME(CustomerID) 
FROM  (select distinct CustomerID from ASCO_RTSDetail) as A ---列名不要重复 
Declare @sql NVARCHAR(4000)
SET @sql='
SELECT * into #temp FROM ASCO_RTSDetail where YEAR(NotifyTime) in (2016) and Month(NotifyTime)>6
SELECT * FROM #temp
AS P
PIVOT
(
    SUM(OrderedQuantity) FOR
    p.CustomerID IN ('+@s+') 
) AS T
drop table #temp'
EXEC(@sql) 

 

6.如果表A的数据可以在B表中匹配到,则删除表A数据

delete tbOriginalItems
 from tbOriginalItems as a
 inner join PriceBookDelete as b
 on a.APBridgeCode = b.APBridgeCode
and a.Region = b.Region
and a.SBU = b.SBU
and a.ItemCode = b.ItemCode

 

7.查看当前正在运行的Job

SELECT
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(ja.last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja with(nolock)
LEFT JOIN msdb.dbo.sysjobhistory jh with(nolock)
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
    ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

 

8.Sql Server 查看存储过程在哪些作业中被调用

SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'Z_USP_InsertCustomerExtendedData'