2

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.

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.