sql server 命令

判断列是否存在

select count(*) from ITSV.his.information_schema.columns where table_name = 'zsb_ctmcallinfo' and column_name = 'ctp_iftax'

判断数据存在

select * from zsb_customer t where not exists(select 1 from zsb_customer where ctm_id=t.ctm_id and ctm_code='001')

导入数据时，判断数据是否已存在

--insert into zsb_customer select case when ([ear_id]='000' and (select count(*) from ITSV.his.dbo.zsb_customer where ctm_id=t.ctm_id and ([ear_id]='000' or [ear_id]=@ear_id)) = 1) then @ear_id else [ear_id] end, [ctm_id] from ITSV.his.dbo.zsb_customer t
    
　 MERGE zsb_customer AS t
   USING (SELECT (case when ([ear_id]='000'and (select count(*) from ITSV.his.dbo.zsb_customer where ctm_id=t.ctm_id and ([ear_id]='000' or [ear_id]=@ear_id)) = 1) then @ear_id else [ear_id] end) as ear_id, [ctm_id] FROM ITSV.his.dbo.zsb_customer t) AS s 
   ON (t.ear_id = s.ear_id)
   WHEN NOT MATCHED THEN
   　　insert values(s.[ear_id]);
    
    --select * from ITSV.his.dbo.zsb_customer where ctm_id='000C00356232'
    --delete from ITSV.his.dbo.zsb_customer where ctm_id='000C00224428' and ear_id='010'

效率比较

select * from (select *, ROW_NUMBER() over(order by ear_id,ctm_code,rvi_code) num from ITSV.his.dbo.zsb_rvinfo) as t where t.num between 10000000 and 11000000 -- rows:100w time:0:4:46
select * from ITSV.his.dbo.zsb_rvinfo WHERE rvi_date >= '2019-10-01 00:00:00' and rvi_date < '2020-02-01 00:00:00' -- rows:330W time:0:1:57

分批取数据

  select * from (select *, row_number()over(ORDER BY ear_id,ctm_code,rvi_code) as num from zsb_rvinfo) as t WHERE t.num>=1 and t.num<=10
  select * from (select *, row_number()over(ORDER BY ear_id,ctm_code,rvi_code) as num from zsb_rvinfo) as t WHERE t.num>=10 and t.num<=20

两个数据库服务器复制数据：

  --创建链接服务器 
    exec sp_addlinkedserver 'ITSV' , '' , 'SQLOLEDB' , '192.168.1.10' 
    exec sp_addlinkedsrvlogin 'ITSV' , 'false' , null , 'sa' , 'sa@1234' 

　  --执行存储过程
　　　 exec batch_import '001'

　　--单独处理"zsb_rvinfo"表
　　　 delete from zsb_rvinfo
　　　 insert into zsb_rvinfo select * from ITSV.DBName.dbo.zsb_rvinfo

　　--以后不再使用时删除链接服务器
　　　 exec sp_dropserver 'ITSV', 'droplogins'

　--跨服务器复制单表数据
　　insert into zsb_rvinfo select TOP (20000) * from openrowset('sqloledb','192.168.1.10';'sa';'sa@1234','select * from ..zsb_rvinfo')

其他相关

　--数据存入临时表
　　select * into ##TempTable from ITSV.DBName.dbo.zsb_customer

　--多条id相同的数据，取日期最新的
　　select * from zsb_customer t where not exists (select 1 from zsb_customer where id=t.id and 日期>t.日期)

　-- 定义游标.
　　DECLARE @ctm_id VARCHAR(10);
　　DECLARE customer_fast CURSOR FAST_FORWARD FOR SELECT ctm_id FROM res_zsb_customer;
　-- 打开游标.
　　OPEN customer_fast;
　　WHILE 1=1
　　　BEGIN
　　   -- 填充数据.
　　　　FETCH NEXT FROM customer_fast INTO @ctm_id;
　　　　if(@ctm_id != '')

　　   -- 假如未检索到数据，退出循环.
　　　　IF @@fetch_status!= 0 BREAK;
　　　　--PRINT @value;
　　　END;