Sql-server – Stored procedure for selecting table rows from a date range used to populate two other tables

sql serverstored-procedures

I have this two tables shipments_docs and dict_shops:

CREATE TABLE shipment_docs (
  doc_id INT PRIMARY KEY,
  doc_num VARCHAR(20) NOT NULL, 

  doc_date DATETIME NOT NULL,  

  shop_id INT NOT NULL           

);
GO
CREATE TABLE dict_shops (

  shop_id INT PRIMARY KEY,   

  shop_name VARCHAR(30) NOT NULL,

  town_id INT NULL,  

  shop_address VARCHAR(100) NOT NULL 

);
GO

And I need to populate them with values from the third table (dbo.shipment_data) that fall into the date range.

CREATE TABLE dbo.shipment_data (

  doc_num VARCHAR(20) NOT NULL,    -- номер документа

  doc_date DATETIME NOT NULL,      -- дата документа

  shop_name VARCHAR(30) NOT NULL,  -- наименование торговой точки

  shop_address VARCHAR(100) NOT NULL  -- почтовый адрес торговой точки

);

I need to implement stored procedure to populate the shipment_docs and dict_shops tables. The procedure should take two parameters – the start date and the end date – which should limit the loaded data set by the doc_date field. 

CREATE PROCEDURE FillTables
@DateFrom DATETIME,
@DateTo DATETIME,
AS
BEGIN
DECLARE @flag INT;
SET @flag = 1;
WITH theDates AS
        (SELECT @DateFrom as theDate
         UNION ALL
         SELECT DATEADD(day, 1, theDate)
           FROM theDates
          WHERE theDate < @DateTo
        )
..........

After reading Google, I realized that in this case it is necessary to use the wif construction. But I can’t understand what the whole query should look like.

Best Answer

I'm not sure why you need a recursive CTE. If you just need to split the data into two tables based on a date range you can do the following:

CREATE PROCEDURE FillTables
(
    @dateFrom DATETIME,
    @dateTo DATETIME
)
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRANSACTION;
BEGIN TRY

    -- GET ONLY THE DATA WE NEED AND CACHE IT
    SELECT doc_num, doc_date, shop_name, shop_address
      INTO #results
      FROM dbo.shipment_data
     WHERE doc_date BETWEEN @dateFrom AND @dateTo;

    -- ADD THE SHOPS TO GENERATE THE ID's 
    -- (COULD USE OUTPUT HERE AS AN ALTERNATIVE)
    INSERT dict_shops (shop_name, shop_address)
    SELECT DISTINCT shop_name, shop_address
      FROM #results
    EXCEPT
    SELECT DISTINCT shop_name, shop_address
      FROM dict_shops;

    -- JOIN ON THE NEWLY CREATED SHOPS TO POPULATE SHIPMENT DOCS
    INSERT shipment_docs (doc_num, doc_date, shop_id)
    SELECT DISTINCT r.doc_num, r.doc_date, s.shop_id
      FROM #results AS r 
      JOIN dict_shops AS s
        ON s.shop_name = r.shop_name
       AND s.shop_address = r.shop_address;

COMMIT;
END TRY
BEGIN CATCH

     IF @@TRANCOUNT > 0
        ROLLBACK;
     THROW;

END CATCH

END
Related Question