sql server 批量循环写入的存储过程

潇锐killer

于 2024-01-26 10:35:33 发布

阅读量141

点赞数

CC 4.0 BY-SA版权

文章标签：服务器运维

本文链接：https://blog.youkuaiyun.com/wangqiaowq/article/details/135860457

文章描述了一个名为sp_RefreshDWDByDateBATCHTEST的SQL存储过程，用于根据指定日期或前一天刷新大数据（dwd_contractlist）表的数据，通过删除旧数据并逐条插入新数据实现数据仓库的定期更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [big_data]
GO
/****** Object:  StoredProcedure [dbo].[sp_RefreshDWDByDateBATCHTEST]    Script Date: 2024/1/26 8:50:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_RefreshDWDByDateBATCHTEST]
   @DateThreshold DATE = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	 -- Use yesterday's date as the default value if no date parameter is provided  
    IF @DateThreshold IS NULL  
        SET @DateThreshold = CONVERT(DATE, GETDATE() - 1)


--- dbo.dwd_contractlist

    -- Insert statements for procedure here

    BEGIN TRY  

		DELETE FROM dbo.dwd_contractlist WHERE dt <= @DateThreshold
		
		
		DECLARE @Counter INT = 1
		DECLARE @MaxIterations INT = 300
		
		WHILE @Counter <= @MaxIterations
		BEGIN
			INSERT INTO dbo.dwd_contractlist ([id],[dt])
			select id,
				 dt
            from ods_contractlist 

			print('dwd_contractlist')

			SET @Counter += 1;
        END
    END TRY  
      

    BEGIN CATCH  

        DECLARE @ErrorMessage NVARCHAR(4000)  
        SET @ErrorMessage = ERROR_MESSAGE()  
          
        RAISERROR (@ErrorMessage, 16, 1)  
          


    END CATCH 

	

END