CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Robin V.
Robin V.

Reputation: 1542

Stored procedure - Insert

Simplified I've got this setup of tables:

Table Users:       Table HolidayTypes:              Table Days:
  ID                       ID                           Year
  Name                TypeName (f.e. 3 types)          TypeID  
                        DefaultValue                   UserId  
                                                       Amount

Now what I want to achieve is to create a stored procedure that can be run at the beginning of each year which will do the following:

For each user, add new records into the Days table for each type of holiday and insert default amount. So I want to get something like this:

Year      TypeId     UserId     Amount
2013        1           1         20
2013        2           1          4
2013        3           1         15
2013        1           2         20
2013        2           2          4
2013        3           2         15

I've got no experience with stored procedures so if someone could help me with this one or help me in the right direction. Any help is much appreciated :)

Upvotes: 1

Views: 136

Answers (1)

sgeddes
sgeddes

Reputation: 62841

This should be the query you're looking for which uses a CROSS JOIN:

INSERT INTO Days
SELECT Year(GetDate()) Year,
  HT.Id TypeId,
  U.Id UserId,
  DefaultValue                   
FROM Users U CROSS JOIN HolidayTypes HT

And here is the sample Fiddle: http://sqlfiddle.com/#!3/15164/1

Which produces these results:

YEAR    TYPEID  USERID  AMOUNT
2013    1       1       20
2013    2       1       10
2013    3       1       5
2013    1       2       20
2013    2       2       10
2013    3       2       5

BTW -- I used different default values when building the fiddle -- update accordingly and your amounts will match those in your post.

Upvotes: 1

Related Questions