[Resolved] HowTo Update records else Insert? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[Resolved] HowTo Update records else Insert?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-30 : 10:10:06

First off I apologize for the long post. OK. I hope I explain this correctly. I've got a table with matching ID's from 2 other tables to tie individual Club Members to Activities that the club has performed. Not all members are required to participate in events so I couldn't base it on ClubID (which would have made things so much easier). So I've got the tblClubActivities set up and the user can add/edit individual entries using a stored procedure. However, I wanted to change it a little so that when they add/edit one Members Activity listing, they can click a checkbox which will then make those same changes for ALL club members (in case they want to include everybody in this change).

So, if a particular club activity has 10 members, but only 2 are "signed up" for a particular activity, when the user edits that activity, if they want, they can "sign up" all of the other members without having to do individual entries. So, for the two that are signed up, it would just Update their listing. But for the other 8, it would have to do an Insert (not an Update).

Here is the layout for the tables and some sample data:

-- Structure tblClub --

CREATE TABLE [tblClub] (
  [clubID] int IDENTITY(1, 1) NOT NULL,
  [clubName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'primary key for the club', 
	'user', 'dbo', 'table', 'tblClub', 'column', 'clubID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'name of the club', 'user', 
	'dbo', 'table', 'tblClub', 'column', 'clubName'
GO

-- Structure tblClubMembership --

CREATE TABLE [tblClubMembership] (
  [cmemID] bigint IDENTITY(1, 1) NOT NULL,
  [clubID] int DEFAULT (1) NOT NULL,
  [studID] int DEFAULT (1) NOT NULL,
  [memID] smallint DEFAULT (1000),
  [joinDate] smalldatetime DEFAULT (getdate()) NOT NULL,
  [leaveDate] smalldatetime DEFAULT ('1/1/1900') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'foreign key to tblClub', 'user', 
	'dbo', 'table', 'tblClubMembership', 'column', 'clubID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'foreign key to tblStudent', 'user', 
	'dbo', 'table', 'tblClubMembership', 'column', 'studID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'4 digit code so students don''t have 
	to use studID', 'user', 'dbo', 'table', 'tblClubMembership', 'column', 'memID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'date they joined', 'user', 'dbo', 
	'table', 'tblClubMembership', 'column', 'joinDate'
GO

EXEC sp_addextendedproperty 'MS_Description', N'date they left the club', 'user', 
	'dbo', 'table', 'tblClubMembership', 'column', 'leaveDate'
GO

-- Structure tblClubActivities --

CREATE TABLE [tblClubActivities] (
  [caID] int IDENTITY(1, 1) NOT NULL,
  [cmemID] bigint DEFAULT (1) NOT NULL,
  [actID] int DEFAULT (1) NOT NULL,
  [caUnits] tinyint DEFAULT (1) NOT NULL
)
ON [PRIMARY]
GO

-- Sample Data tblClub --

SET IDENTITY_INSERT [tblClub] ON
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (1, 'No Club')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (2, 'Astronomy')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (3, 'Chess')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (4, 'PoliSci Leaders')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (5, 'Save The Earth')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (6, 'PETA')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (7, 'Spanish')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (8, 'German')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (9, 'S.P.E.W.')
GO

SET IDENTITY_INSERT [tblClub] OFF
GO

COMMIT
GO

-- Sample Data tblClubMembership --

SET IDENTITY_INSERT [tblClubMembership] ON
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (1, 1, 1, 1000, '1/1/1900', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (2, 4, 8, 1205, '4/27/2004', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (3, 7, 12, 1105, '8/12/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (4, 5, 14, 1206, '9/15/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (5, 2, 2, 1800, '10/10/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (6, 8, 5, 1305, '11/10/2004', '9/18/2005')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (7, 6, 9, 1417, '4/12/2004', '6/12/2004')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (8, 3, 6, 1703, '10/11/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (9, 7, 11, 2508, '5/18/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (10, 5, 12, 1109, '8/12/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (11, 6, 7, 1418, '12/3/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (12, 6, 6, 1211, '12/12/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (13, 7, 5, 1054, '12/12/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (14, 4, 11, 1313, '12/13/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (15, 4, 9, 1112, '12/13/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (16, 2, 5, 1497, '12/15/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (17, 5, 2, 1900, '12/17/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (18, 5, 3, 1901, '12/17/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (19, 5, 10, 1902, '12/17/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (20, 5, 13, 1903, '12/21/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
	[joinDate], [leaveDate])
VALUES 
  (21, 5, 11, 1904, '12/21/2005', '1/1/1900')
GO

SET IDENTITY_INSERT [tblClubMembership] OFF
GO

COMMIT
GO

-- Sample Data tblClubActivities --

SET IDENTITY_INSERT [tblClubActivities] ON
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (1, 1, 1, 0)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (2, 4, 14, 2)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (3, 10, 14, 2)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (4, 6, 71, 4)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (5, 3, 53, 2)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (6, 9, 53, 2)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (7, 7, 36, 3)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (8, 11, 36, 3)
GO

INSERT INTO [tblClubActivities] ([caID], [cmemID], [actID], [caUnits])
VALUES 
  (9, 12, 36, 3)
GO

SET IDENTITY_INSERT [tblClubActivities] OFF
GO

COMMIT
GO

-- Indices -- 

ALTER TABLE [dbo].[tblClub]
ADD CONSTRAINT [PK_tblClub] PRIMARY KEY CLUSTERED ([clubID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [PK_tblClubMembership] PRIMARY KEY CLUSTERED ([cmemID])
ON [PRIMARY]
GO

CREATE INDEX [tblClubMembership_idx1] ON [dbo].[tblClubMembership]
  ([clubID])
ON [PRIMARY]
GO

CREATE INDEX [tblClubMembership_idx2] ON [dbo].[tblClubMembership]
  ([studID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_uq] UNIQUE ([memID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubActivities]
ADD CONSTRAINT [PK_tblClubActivities] PRIMARY KEY CLUSTERED ([caID])
ON [PRIMARY]
GO

-- Foreign Keys --

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_fk1] FOREIGN KEY ([clubID]) 
  REFERENCES [dbo].[tblClub] ([clubID])
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_fk2] FOREIGN KEY ([studID]) 
  REFERENCES [dbo].[tblStudent] ([studID])
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

Using that information, here is the current tblClubActivities list and the number of students in each Club:

'    current tblClubActivities

caID   ClubMember                          Activity                Units
 1      Empty_Record                         Empty_Record                0
 2      Save The Earth (Mila Kunis)          CFC Sit-In                  2
 3      Save The Earth (Evangeline Lilly)    CFC Sit-In                  2
 4      German (Jordana Brewster)            Student Sign-Up             4
 5      Spanish (Evangeline Lilly)           Fund Raiser (114)           2
 6      Spanish (Jennifer Morrison)          Fund Raiser (114)           2
 7      PETA (Lake Bell)                     City Hall Demonstration     3
 8      PETA (Rhona Mitra)                   City Hall Demonstration     3
 9      PETA (Anna Kournikova)               City Hall Demonstration     3



'    current tblclub totals (for Clubs that have members)

Astronomy (2)
Chess (1)
German (1)
PETA (3)
PoliSci Leaders (3)
Save the Earth (7)
Spanish (3)

So, for example, let's say that PETA wants to update it's activities listing for City Hall Demonstartion. It wants to credit 5 units to each student instead of the current 3. For that it would basically be an "update" for each of the three Members (because all three are already in there). But let's say that Save The Earth wants to change the credits for ALL the Members on the activity (CFC Sit-In) (changing the units from 2 to 3). That would require updating the existing 2 then inserting the other 5. And, of course, if PETA wanted to add a different Activity (for all 3 Members), then it would be an insert for all three.

I want to do this through a stored procedure. So I started writing it where the first part would get a listing of all cmemIDs for the club doing the update/insert.

'    procedure to get all cmemID's for the club in question

CREATE PROCEDURE admin_sp_GetMobileList
(
@in_intClubID INTEGER,
@in_intUnit TINYINT,
@in_intActID INTEGER
)

AS

SET NOCOUNT ON

BEGIN

     SELECT    cmemID
     FROM      tblClubMembership 
     WHERE     clubID = @in_intClubID

END

My plan was to then Loop through the recordset using each cmemID. But when I started reading the threads about Looping I've gotten the impression that they are Evil and should be avoided at all costs :) Seriously, it seems that most people prefer to avoid Loops at all cost because of the hit you take in performance. But I don't know how else I would write this in. Actually, even if I have to Loop I don't know how to go about that. I thought that the If Exists (checking for matching cmemID and actID) ... approach was the way to go but without knowing the cmemID (which would require Looping, I think), I don't know how I would do it.

Any idea's? I've searched the forums and looked through the OnLine Books but the stuff that seemed like it might be what I need was, well, confusing. I'm apologize if this is a really dumb question or really simple but I've been working on it for 2 days and just can't get my head wrapped around how to do this.

DTFan
Ever-hopeful programmer-in-training

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-30 : 10:39:54

You are making this way too complex.

1. Update the existing rows with one update statement.

update tblClubActivities
set
	caUnits = 5
where
	actID = 1	and
	caUnits <> 5

2. Insert new rows that are not already there with one insert statement.

insert into tblClubActivities
	(
	[cmemID],
	[actID],
	[caUnits]
	)
select
	a.[cmemID],
	[actID]		= 1,
	[caUnits]	= 5
from
	tblClubMembership a
	left join
	tblClubActivities b
	on a.[cmemID] = b.[cmemID]
where
	b.[cmemID] is null

CODO ERGO SUM

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-30 : 12:27:54

quote:
Originally posted by Michael Valentine Jones

You are making this way too complex.
CODO ERGO SUM

You're right. I think that is my biggest weakness in terms of SQL (thus far ... and that doesn't include the actual language itself

). I start off with something pretty simple and by the time I've tried making it work it's evolved into something monstrous and complicated. Hopefully at some point I'll be able to start seeing the more direct approach to handling these things.

Also, thank you very much for the help. And now it's time to mark this thread "Resolved".

Thanks again.

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources