Insert multiple rows in Database table using T-sql

Question

I want to create SQL statement (probably a stored procedure) to insert multiple rows in a database table. For the dates in one year depending on the weeks number selected.

For example: if Week number selected = 4

The sql statement should insert the new rows in database table for the current date adding 4 weeks to current date for each row as follows:

CompanyID  DateStart                  ServiceType

101       todayDate                     0091
101       TodayDate + 4weeks            0091
101       TodayDate + 8weeks            0091
101       TodayDate + 12weeks           0091
.               .                          .
.               .                          .
.               .                          .
101       TodayDate + #weeks            0091
            (until this yearEnd only)

** Please NOTE:

1. Before the above script is executed I want to check if there are any records in the same database table for previous year for the company (#101) the serviceType (#0091). If any records exists I want to delete those records.

2. I also want to make sure if for the service type (#0091) for the company(101) already exists in the current year, then I should not insert the new rows in the database table.

Thank you so much for your help for taking time and understanding my question to produce appropriate result.

Are you on SQL Server 2005 or 2008 ?? You have both tags..... — marc_s
– marc_s, Commented May 25, 2011 at 15:32
Hi Marc_S Excellent and very simple to the point. I was thinking I might need to write big query for this logic to implement. I just want to make sure can I write insert satement before the 'Select' stament is executed. Thanks a lot — desi
– desi, Commented May 25, 2011 at 16:28

marc_s · Accepted Answer · 2011-05-25 15:45:22Z

3

You could try something like this to generate the rows to be inserted:

DECLARE @CurrentYear INT = YEAR(GETDATE())

;WITH DatesToInsert AS
(
    SELECT 
        101 AS 'CompanyID',
        GETDATE() AS 'TodayDate', 
        '0091' AS 'ServiceType'

    UNION ALL

    SELECT 
        101 AS 'CompanyID',
        DATEADD(WEEK, 4, dti.TodayDate) AS 'TodayDate', 
        '0091' AS 'ServiceType'
    FROM      
        DatesToInsert dti
    WHERE
        YEAR(DATEADD(WEEK, 4, dti.TodayDate)) = @CurrentYear
)
SELECT * FROM DatesToInsert

From that CTE (Common Table Expression), you can insert values into a table and check all the other requirements you have. And of course, you can make the number 4 in the DATEADD call configurable, e.g. as the parameter of a stored proc that contains this CTE to handle the inserts.

edited May 25, 2011 at 15:45

answered May 25, 2011 at 15:36

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert multiple rows in Database table using T-sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related