learning sql need help with a simple script

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

General SQL Server Forums

New to SQL Server Programming

learning sql need help with a simple script

Author

Topic

cutlass1972
Starting Member

12 Posts

Posted - 2008-12-08 : 12:15:02

I have a need to combine 2 tables where there is a constraint that requires that no 2 values are the same in the userid column. I have multiple instances of where the userid exists in both tables.

I would like to write a script that will add a character to the value of the table I am combining from to make it unique, but I don't know how?

I have come up with this so far

update peru1.dbo.users set userid = 'name' WHERE (userid in (select userid from peru2.dbo.users))

this will change the names of any value it comes up with to "name", obviously this is not going to work, but at least I have gotten to where my script will identify duplicates.

Now how do I create a variable that will leave the value of the duplicate the same, and tack on another charictor?

can anyone assist?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-12-08 : 12:18:34

easy,
just add a number
here's a template

declare @c bigint
set @c=1
update mytable set @c=@c+1, mycol=@c
where mycol is null

Or in your case:

declare @c bigint
set @c=1
update peru1.dbo.users
@c=@c+1,
set userid = 'name' + cast(@c as varchar(10))
WHERE (userid in (select userid from peru2.dbo.users))

cutlass1972
Starting Member

12 Posts

Posted - 2008-12-08 : 13:34:16

wow! thanks for the quick response! I cannot get it to work though, I get 2 errors.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@c'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

I also notice that you kept my 'name', I was just using that more or less as a place holder, what I would prefer is that it keep the existing value and tacks a character on the end, like if it came up with 2 values that say "john" it tacks a letter on the end of the duplicate, like "johnr"

I apologize if this is preschool stuff, but I am trying to learn more or less from the ground up. I REALLY do appreciate any assistance.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 13:42:43

If you have the ID + 1 then you'll still end up with duplicate IDs. The best solution would be reassign them with unique IDs.

What is the purpose of the new Combined table??

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-08 : 13:43:55

If I were you I would look at implementing 1st Normal Form. Since you are new (per your post) this may be a good approach to take.

Quite simply 1st normal form is a simple process by which you create a new table to remove repeating fields. The new table and the old table are linked by using primary key and foreign key relationships. The new table will contain the unique ID's you want and the old table can retain the repeating fields.

it may be worth looking into especially if your table will grow large. It also helps establish a good database design (which alleviates problems in the future).

r&r

cutlass1972
Starting Member

12 Posts

Posted - 2008-12-08 : 13:55:09

If all I wanted to do was to delete duplicates, I could wipe them out very easily by doing this
DELETE peru2.dbo.users
WHERE (keyid IN (select keyid from peru1.dbo.users))

but I need the second occurrence of the user in addition to the first

let me provide a little more info, I think it may help. the tables I am working on are 2 databases from 2 different branches that we have been using for a number of years that now we need combined.

There is a need to have multiple occurrences of a single user in this table, for example the same user has 2 different addresses. Obviously I cannot have the user in under the same name 2 times, so I would like to have the second occurrence merged into the first table, but with an additional character tacked on so that I meed the criteria of the constraints of the table, but if I query the table I can have both occurrences pop up in succession.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-08 : 14:03:59

Again, this just screams normalization to me. I believe it is worth the effort in learning and implementing in your DB structure.

I seriously recommend you look into it. If you start hack coding fixes now then it will only cost you later. Why not setup a simple elegant approach which will save you maintenance headaches later?

It is not that difficult to do. Google is your friend here w.r.t looking it up!

r&r

cutlass1972
Starting Member

12 Posts

Posted - 2008-12-08 : 14:16:53

well, I understand what you are saying, but the circumstances limit how much control I have there. The circumstances I am quoting you I brought up for simplification of the scenario.

In reality these databases are created by a piece of software that my company makes that we sell, now I have 2 branches of a company in the field that have been in independent operation for some time that now want one centralized DB. So I have to come up with a way to merge their existing DB's in such a way that they loose no data.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 14:28:22

Say branch 1 has more records than branch 2, make Branch 1 the primary table and only check duplicated userids on branch2:


--Step1 Insert all records from Branch 1 
INSERT INTO NEW_BRANCH
SELECT * FROM BRANCH1

--Step2 Insert non dup Userids from Branch 2
INSERT INTO NEW_BRANCH
SLECT * FROM BRANCH2 Z
WHERE NOT EXISTS (SELECT 1 FROM BRANCH1 WHERE USERID = Z.USERID)

--Step3 Add a special Character to the end of USERID (assume USERID is varchar)
INSERT INTO NEW_BRANCH
SELECT USERID+'_BRANCH2', COL2, COL3 ....
FROM BRANCH2 Z 
WHERE EXISTS (SELECT 1 FROM BRANCH1 WHERE USERID = Z.USERID)

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-08 : 14:30:04

Tell you what.. Read "Problems addressed by Normalization" here:
[url]http://en.wikipedia.org/wiki/Database_normalization[/url]
The first paragraph is exactly what you are describing. You will have these problems if you do not normalize. If you can create tables then you have enough control.

You can implement this without losing any data. The new table would contain the merged data from the other two databases. This is the table you would query & update in the future.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-12-09 : 08:02:57

quote:
Originally posted by cutlass1972

wow! thanks for the quick response! I cannot get it to work though, I get 2 errors.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@c'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

I also notice that you kept my 'name', I was just using that more or less as a place holder, what I would prefer is that it keep the existing value and tacks a character on the end, like if it came up with 2 values that say "john" it tacks a letter on the end of the duplicate, like "johnr"

I apologize if this is preschool stuff, but I am trying to learn more or less from the ground up. I REALLY do appreciate any assistance.

declare @c bigint
set @c=1
update peru1.dbo.users
set @c=@c+1,
set userid = 'name' + cast(@c as varchar(10))
WHERE (userid in (select userid from peru2.dbo.users))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources