Like IDENTITY only not like IDENTITY

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)

Like IDENTITY only not like IDENTITY

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 07:50:53

I have a table of emails sent, and I'd like a column to hold a unique ID for the rows which were involved in a single "group email" transaction. (e.g. all email sent by bob to the distribution list regarding xxxx).

Any unique ID which would meet a where condition to find all rows sent by Bob:

WHERE EmailGroup = @EmailGroup

So, how do I come up with a unique number?

SELECT @EmailGroup = MAX(EmailGroup)+1 From EmailTable

Or, to be atomic about the whole thing...

INSERT INTO EmailTable (EmailGroup, etc...)
SELECT MAX(EmailGroup)+1, etc FROM EmailTable

Suggestions appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 07:55:03

Update EmailTable
set EmailGroup = (SELECT MAX(EmailGroup)+1 FROM EmailTable)
from EmailTable
inner join inserted i on i.id = t1.id

in a trigger should do nicely, no?

edit: forgot the join to inserted

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 08:07:32

Not a bad idea. I'm trigger phobic however. But this is a pretty mild trigger, not requiring inspection of the incoming data that is.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 08:09:02

well don't be a trigger phobic

they're just another sproc's....

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 08:15:22

Is there a way to cripple the trigger to insert a predetermined value?

I'm wondering how I would populate this new table with values from an old table that already has groups??

INSERT INTO NEWTABLE (GroupEmail, etc...)
SELECT (GroupEmail, etc)
FROM OLDTABLE

Maybe the trigger could check to see if GroupEmail is provided in the insert?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 08:24:26

insert the stuff into a new table without group numbers.
and the update the correct groups with new group values.
there was a thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45410

then create a trigger on the table and let it do its magic.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-03 : 08:43:32

i'm not 100% sure what you need, but:

Do you need a table of EmailGroups? Add a row to that table whenever a new EmaiLGroup is being sent, get back an Identity, and then use that value to "stamp" as subsequent emails sent in that batch with the same GroupID ? (That way, you can store info about that particular group, and also have RI)

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 16:42:21

I'd considered a table of EmailGroups... right or wrong, I'm foraging ahead with a trigger solution. In part, because the trigger nicely solves this problem and in part, because another table "EmailGroups" wouldn't bring anything to the party, and it would take a couple of statements (I'm a minimalist) to do an INSERT using a join table.

Here's the trigger I used...

CREATE TRIGGER TR_EM_Queue_GroupID
-- Add a GroupID value if this INSERT is regarding an Administrative Group Email
	ON	dbo.EM_QUEUE
	FOR	INSERT
AS
SET NOCOUNT ON
IF	EXISTS (SELECT 1 FROM INSERTED WHERE EmailType = 'GroupEmail' AND GroupID = 0) BEGIN -- Group email insert?

	DECLARE	@MaxEQID	INT
	SELECT	@MaxEQID = MAX(EQID) -- Use the first ID as the Group Email ID
		FROM	INSERTED

	UPDATE	EQ
		SET	GroupID = @MaxEQID
	FROM	dbo.EM_QUEUE EQ
	INNER JOIN INSERTED I
		ON	I.EQID	= EQ.EQID
	WHERE	I.GroupID = 0 -- Safety - don't overwrite existing Group Email IDs
END
GO

Inspects INSERTS into table EM_Queue. Most the inserts will be single emails for which the GroupID is always zero (the default value of the GroupID column).

Occasionally, an administrator will email a group of as many as 10,000 users. This trigger will change the column GroupID from zero to the EQID (IDENTITY) of the last row inserted, replacing what would be some mighty difficult code to pull a similar stunt without a trigger.

SELECTING all these records (for historic reporting) is simplified now by finding all records matching GroupID.

Setting GroupID to the last ID of the INSERT recordset wasn't an accident. It allows retrieval of the newly created GroupID using

SET @GroupID = SCOPE_IDENTITY()

right after the INSERT statement.

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 16:46:26

Is there a way to combine these two statements

	DECLARE	@MaxEQID	INT
	SELECT	@MaxEQID = MAX(EQID) -- Use the first ID as the Group Email ID
		FROM	INSERTED

	UPDATE	EQ
		SET	GroupID = @MaxEQID
	FROM	dbo.EM_QUEUE EQ
	INNER JOIN INSERTED I
		ON	I.EQID	= EQ.EQID

using a join? Specifically, not like this:

	UPDATE	EQ
		SET	GroupID = (SELECT MAX(EQID) FROM INSERTED)
	FROM	dbo.EM_QUEUE EQ
	INNER JOIN INSERTED I
		ON	I.EQID	= EQ.EQID

I get nervous about the execution plan leaving the MAX aggregate in a loop with the second solution. Maybe I shouldn't be concerned?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 16:47:19

Would a GUID like NEWID() be useful?

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 16:49:14

Thought of it. Ignored it maybe for the wrong reason. It's huge isn't it?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 16:49:32

It nicely converts to CHAR(36).

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 16:56:19

I use NEWID() to pick random records, but being a minimalist, it's hard to go with NEWID which takes 36 characters (eight INTs) when there's another not-too-difficult solution. It would work fine for the purpose though.

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-03 : 17:04:31

What about

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

Does that help?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-03 : 17:17:58

Thanks Brett. I looked it over. It seems like a lot of code if this does what the trigger I posted does. I'm a trigger newbie. Looks like some code to implment an IDENTITY and it could be modified to do this.

I don't know. Minimalism.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-03 : 18:24:29

Don't dismiss GUID just because they are large. The advantage of being able to assign the value of the key without the help of the DBMS is a big issue for me. The current project I am doing uses GUID's instead of IDENTITY columns. Coding the front end is so much easier using GUID's vs Identity.. it is for me any way...

DavidM

"Always pre-heat the oven"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-03 : 20:18:54

GUIDs are NOT char(36)'s, they are binary(16)'s.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-03 : 20:23:48

Who said they were CHAR(36)'s ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources