Using a lookup table to modify group id 1

[dparrott]

Hey all,

I'm in the process of updating custom code for one of our purchased apps. The vendor had all the users and groups in one table called people, and in the new version, split that out into two tables called users and groups. In the old version, all the users and groups had positive numbers for IDs. In the new one, all the groups have to have negative numbers for ID's, and all the users have positive numbers.

I'm trying to migrate the users into the users table (new version) from the people table (old version). One of the columns in the user table is "group_id", which is a foreign key for the groups table.

I have a lookup table which has a PK, the old group_id, the new group_id, and the group name.

My question is how to write the insert so that the new group_id is inserted instead of the old group_id (which fails the constraints since the old one is positive and the new one is negative).

Thanks in advance,

Danny

 

[hmckillop]

First things first, why on earth do you want to have a negative ID for a group ID, its crazy idea.

In answer to your main question, create an additional temporary field on your new group with the old group id.

insert you users into the new user table using the old group id, then run an update statement to apply the new id.

Though still strikes me as a weird thing as I guess if both were in the same table with a unique id in first place, you could retain these both if you didnt insist on using a negative number for group id.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dparrott]

I completely agree that negative values is completely crazy.

We are adding some custom tables, views, and procedures to the database to add functionality to the purchased product. Therefore, we have to live with some of their choices (such as negative values for groups). We really don't want to modify their tables if at all possible.

Your suggestion of adding the users with the old, then changing to the new won't work. There's a constraint that checks to make sure the group id is in the group table (which I can't add positive values to, without messing with their table).

I've been doing more research on this, and it appears that a stored procedure might be the solution. At least it'll allow me to use variables (since I'm practically a SQL noob, I haven't gotten very far into it). It seems I'll need some type of loop.

Basic flyover would be thus:

1. Get the data from the old people table (only pull the users, not the groups.

2. for each row,
2a. put all the columns into variables
2b. find the old group id in the lookup table, and save that to a variable with what the new group is
2c. insert the variables into the new users table using the new group id, instead of the old one.

Is this possible to do?

Thanks,

Danny

 

[SQLSister]

Never consider working in a loop! Loops are bad things in SQL Server. Learn to think in terms of sets of data not individual record processing!

You say you have a lookup table with the old and new values. Use that to populate the new users and groups tables. First the groups table must be populated with the new groups or you will have a constraint issue onthe user table. Once you are sure the new groups ar in the table, then do something like this :

insert users (Id, username, groupid)
select p.id, p.username, l.newgroupid 
from people p
join lookuptable l on p.id = l.id

(Still sounds like a poor design BTW as there should be three tables, users, groups and user groups to handle if a user is in multiple groups)

"NOTHING is more important in a database than integrity." ESquared

 

[dparrott]

SQLSister,

I had a feeling that I was making this WAY more complicated than necessary. I haven't done any DB stuff since college, while I have kept up a bit with other programming stuff, which is almost certainly why I wasn't able to come up with this on my own.

I think two tables was an improvement from the one they had it in, but there is no way for a user to be in more than 1 group at a time, so the third table isn't necessary (now, at least. This is a printer controlling application to monitor how much people print, and prevent them after they've gone over a quota. Multiple group membership isn't something super useful in that scenario).

Thanks and have a star!