CODEWITHSUNDEEP
sqlsql-server
Powellellogram
Powellellogram

Reputation: 436

How to Create a Row for Each Row from Another Table

I have a Users table and a Memberships table. A User can have many Memberships, paid or unpaid recurring on a yearly basis. I want to create a new row in Membership for each User in Users, as a Free member.

Membership is currently empty so I'll have to create new ids for each row automatically.

Membership is laid out as: 

[Id]
[StartDateTime]
[EndDateTime]
[Frozen]
[MembershipType]
[PaidDateTime]

And User is 

[Id]
  ,[AspUserId]
  ,[Title]
  ,[Forename]
  ,[Surname]
  ,[Email]
  ,[Dob]
  ,[DateCreated]
  ,[AddressId]
  ,[PhoneNumber]
  ,[Gender]
  ,[Nationality]
  ,[WMSFPlayerId]
  ,[ProfileImageURL]
  ,[FavouriteGame]
  ,[Bio]
  ,[Username]
  ,[LastLogin]
  ,[WMSFPlayerId_Confirmed]
  ,[MembershipId]

In Membership:

How can I insert a Free Membership into the Membership table for each User I have in Users? I'll have to also update the Users table with the new MembershipID that was created for them.

Upvotes: 0

Views: 81

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You would use insert . . . select. Something like this:

insert into memberships(id, StartDate, EndDate, Frozen, MembershipType, PaidDateTime)
    select u.aspuserid, getdate(), dateadd(year, 1, getdate()), 0, 0, null
    from users u;

Upvotes: 1

Dan Hollinger
Dan Hollinger

Reputation: 76

First, I would like to point out that your table definitions won't support the requirement of having multiple memberships per user. What you have right now is multiple users per membership. The user table should not have the MembershipID field. It should be removed from the User table and a UserID field should be added to the Membership table. Once that's done something like, 

INSERT INTO Membership (Id, StartDateTime, EndDateTime, Frozen, MembershipType)
SELECT Id, GETDATE(), GETDATE(), 0, 'Free' FROM User

Obviously you'll need to change the values based on your requirements.

Upvotes: 2

Related Questions