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.