sql常用语句

1.将一张表的结构和数据复制给另外一张新表 SELECT * INTO Z_CustomerExtendedData1 FROM Z_CustomerExtendedData

 

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

 

3.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

 

4.查询某个表被哪些存储过程（以下简称 SP）使用到

select distinct object_name(id) from syscomments where id in
(select object_id from sys.objects where type ='P') and text like'%Z_NeedCloseMO%'

 

5.将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)

转载于:https://www.cnblogs.com/fqdt/p/6846896.html