Inserting and keeping names unique

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)

Inserting and keeping names unique

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-05-16 : 19:14:35

DDL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[items]
GO

CREATE TABLE [dbo].[items] (
[item_id] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [int] NOT NULL ,
[name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[items] ADD
CONSTRAINT [PK_items] PRIMARY KEY CLUSTERED
(
[item_id]
) ON [PRIMARY] ,
CONSTRAINT [IX_items] UNIQUE NONCLUSTERED
(
[user_id],
[name]
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Numbers]
GO

CREATE TABLE [dbo].[Numbers] (
[Number] [int] NOT NULL
) ON [PRIMARY]
GO

SET IDENTITY_INSERT items ON

insert into items (item_id,user_id,name) values (1,1,'atst1-nobooklinks');
insert into items (item_id,user_id,name) values (2,1,'atst2-fullother');
insert into items (item_id,user_id,name) values (3,1,'atst3-fullthis');
insert into items (item_id,user_id,name) values (4,1,'atst4-fullthisfullother');
insert into items (item_id,user_id,name) values (5,1,'atst5-chapteronlythis');
insert into items (item_id,user_id,name) values (6,1,'atst6-chsec');
insert into items (item_id,user_id,name) values (7,1,'atst1-nobooklinks (Copy)');
insert into items (item_id,user_id,name) values (8,1,'1234567890123456789012345');
insert into items (item_id,user_id,name) values (9,2,'atst3-fullthis');
insert into items (item_id,user_id,name) values (10,2,'atst3-fullthis (Copy)');
insert into items (item_id,user_id,name) values (11,2,'atst3-fullthis (Copy 2)');
insert into items (item_id,user_id,name) values (12,2,'atst6-chsec');
insert into items (item_id,user_id,name) values (13,2,'atst6-chsec (Copy)');
insert into items (item_id,user_id,name) values (14,2,'atst1-nobooklinks');
insert into items (item_id,user_id,name) values (15,2,'1234567890123456789012345');

SET IDENTITY_INSERT items OFF

insert into Numbers (number) values (0)
insert into Numbers (number) values (1)
insert into Numbers (number) values (2)
insert into Numbers (number) values (3)
insert into Numbers (number) values (4)
insert into Numbers (number) values (5)
insert into Numbers (number) values (6)
insert into Numbers (number) values (7)
insert into Numbers (number) values (8)
insert into Numbers (number) values (9)
insert into Numbers (number) values (10)

--

I'm trying to copy items from one user_id's pool to another. If same name exists already in the second user's pool of items,
append ' (Copy)' to the name, if that exists append ' (Copy 2)',' (Copy 3)' etc until it is unique. Also, have to be careful to not overflow 25 char limit of field. My current trimming solution is sloppy because it always trims 9 chars when it may not always need to but I was concerned about slowing it down too much with special cases. Also, my proof of concept for the insert is almost working but flawed because it doesn't take into account one of the renames matching a name in the original user. It's also not terribly fast.

So, any suggestions? Am I crazy to try to do this in T-SQL when I could easily do it in the app, albeit slowly and one row at a time? I thought I'd give it a shot :) Thanks.

--

Sample Queries:

begin tran t1

-- just show what we'll be trying to insert
-- this is a horribly slow proof of concept and still flawed because it doesn't take into account renames existing in the original source
select cesrc.item_id, cesrc.name,
number, LEFT(cesrc.name,16)+CASE WHEN number=0 THEN '' ELSE ' (Copy'+CASE WHEN NUMBER=1 THEN '' ELSE ' '+CAST(number as varchar(5)) END+')' END
from items cesrc
left outer join numbers n on n.number=
(select min(number) from numbers where LEFT(cesrc.name,16)+CASE WHEN number=0 THEN '' ELSE ' (Copy'+CASE WHEN NUMBER=1 THEN '' ELSE ' '+CAST(number as varchar(5)) END+')' END not in
(select LEFT(name,16) from items where user_id=2)
)
where cesrc.user_id=1

/* Returns
8 1234567890123456789012345 1 1234567890123456 (Copy)
1 atst1-nobooklinks 1 atst1-nobooklink (Copy)
7 atst1-nobooklinks (Copy) 1 atst1-nobooklink (Copy)
2 atst2-fullother 0 atst2-fullother
3 atst3-fullthis 1 atst3-fullthis (Copy)
4 atst4-fullthisfullother 0 atst4-fullthisfu
5 atst5-chapteronlythis 0 atst5-chapteronl
6 atst6-chsec 1 atst6-chsec (Copy)
*/

-- try to insert it (fails because it attempts to insert atst1-nobooklinks (Copy) twice)
insert into items (user_id,name)
select 2, LEFT(cesrc.name,16)+CASE WHEN number=0 THEN '' ELSE ' (Copy'+CASE WHEN NUMBER=1 THEN '' ELSE ' '+CAST(number as varchar(5)) END+')' END
from items cesrc
left outer join numbers n on n.number=
(select min(number) from numbers where LEFT(cesrc.name,16)+CASE WHEN number=0 THEN '' ELSE ' (Copy'+CASE WHEN NUMBER=1 THEN '' ELSE ' '+CAST(number as varchar(5)) END+')' END not in
(select LEFT(name,16) from items where user_id=2)
)
where cesrc.user_id=1

rollback tran t1

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-17 : 07:09:01

A good rule of thumb is, do NOT allow bad data into the DB. If it is a dupe, kick it out, dont store it as "copy2". Redundant data <> relational.

*need more coffee*

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-05-17 : 09:42:21

It's not redundant data. The second user want's a copy of an item so they can work on it and change it as they please w/o affecting the first user's item. I have a constraint making sure each user's items have a unique name and I'd like to keep that in place and automatically give it a new name if there's a collision so a batch copy can be nice and fast and they can then go back and rename things if they choose rather than only allowing a copy one at a time and prompting for new names or other inelegant solutions.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-17 : 12:31:33

Maybe one of the design gurus will read this one. Something dont sound right.....

*need more coffee*

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-19 : 21:15:12

quote:
I'm trying to copy items from one user_id's pool to another. If same name exists already in the second user's pool of items,
append ' (Copy)' to the name, if that exists append ' (Copy 2)',' (Copy 3)' etc until it is unique. Also, have to be careful to not overflow 25 char limit of field.

Just how many copies of data are you willing to make here?

This could get out of hand quick!

quote:
It's not redundant data. The second user want's a copy of an item so they can work on it and change it as they please w/o affecting the first user's item.

Are you saying that each copy will then be unique

and the changes to one of the copies doesn't get updated to the original?

If so, and these are 'pooled' items, what about making a master table for ALL unique items (i.e., the first copy), and then allowing users to pull from that as needed?

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources