sqlserver存储过程检查表是否存在，存在即删除，不存在创建，家while遍历插入数据

本文主要是介绍sqlserver存储过程检查表是否存在，存在即删除，不存在创建，家while遍历插入数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

use DFPVHRDB
go
--检查是否存在该表，存在即删除
if exists(select * from sysobjects where id = OBJECT_ID('DFPVHRDB..zyxATT05newPerson') and OBJECTPROPERTY(id,'IsUserTable') = 1)
print 'exists'
drop table DFPVHRDB..zyxATT05newPerson

go
--检查是否存在该表，不存在即创建新表
if not exists(select * from sysobjects where id = OBJECT_ID('DFPVHRDB..zyxATT05newPerson') and OBJECTPROPERTY(id,'IsUserTable') = 1)
print 'not exists'
select * into DFPVHRDB..zyxATT05newPerson from DFPVHRDB..ATT05 A
where A.YearInt=2021 and A.MonthInt = Month(GETDATE()) ;
--删除表数据
delete from DFPVHRDB..zyxATT05newPerson
print 'delete'
go

declare
@i int, --定义循环条件
@YearInt datetime,--定义年份
@MonthInt datetime,--定义月份
@RecDate datetime,
@PersIDList varchar(max);--定义ｃｈａｒｉｎｄｅｘ的格式

print 'declare'
set @i = 1;
print @i
set @YearInt=2021;
print @YearInt
set @MonthInt=11;
print @MonthInt
set @RecDate = '2021-11-01 00:00:00.000';
print @RecDate
print 'set'
while @i <= 30

begin
Insert zyxATT05newPerson(YearInt,MonthInt,RecDate,EMPID,PersID,Name,DeptCode,DeptName,PersType,PersClass,YCQT,L1DeptName,L2DeptName,L3DeptName,ZGQK,YCQXS,JobName2,JobCode,JobName)
Select 2021,11,@RecDate,A.EMPID,A.PersID,A.Name,A.DeptCode,A.DeptName,A.PersType,A.PersClass,0,A.L1DeptName,A.L2DeptName,
A.L3DeptName,A.ZGQK,Case datepart(weekday,@RecDate) when 1 then 0 when 7 then 0 else 8 end,A.AllJobName,A.JobCode,A.JobName
From EMP01 A Left Join EMP12 B On A.PersID=B.PersID where B.YearInt='2021' and B.MonthInt='11' and B.AppStatus='审批通过';

set @i = @i + 1 ;
print @i
set @RecDate = @RecDate + 1;
print @RecDate


end

这篇关于sqlserver存储过程检查表是否存在，存在即删除，不存在创建，家while遍历插入数据的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！