SQL创建表,删除表,事务,循环

//创建表

CREATE TABLE [dbo].[RegisterOilDate](
 [AccountOpenDate] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [CustomerID] [decimal](18, 0) NULL,
 [OilCardID] [decimal](20, 0) NULL,
 [IDCardType] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [IDCard] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [CardHolder] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Telephone] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [CardType] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [CardStatus] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

 

//删除表

drop  table  RegisterOilDate

 

//事务

begin
 set nocount on;
 begin try
   begin transaction;
     ----执行
   commit transaction;
 end try
 begin catch
   if @@trancount >0
    begin
      rollback transaction;
      ---返回错误号
    end
 end catch
end

 

----------循环
 declare @Rowcount int
 declare @i int
 set @i=0
 select @Rowcount=count(*) from @tblPK

 while @i<@Rowcount
 begin
    select top 1 @dealerid=d.dealerid,@Name=d.dealerName,@MasterID=MasterDealerID from V4_Dealer  d
    join @tblPK b on d.dealerid=b.PK  where d.dealerid not in(
    select top (@i) PK from @tblPK b )

end