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.
Author |
Topic |
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-07-31 : 15:26:24
|
Hi,I want to get some advices on how to deal with this.I need to periodically send a set of data to a vendor. They need to have a column what is an unique identifyer but they cannot handle 16 digit GUI.So I have this my_old_person table with person_GUI as uniqueidentifier. It got 200K rows in it. I am thinking creating a staging utility table:Create table my_new_person(person_GUI varchar(255) not null,<--this will contains the same GUI from the oldPerson_ID nvarchar(20))<--send tis to vendorAt the beginning of my nightly ETL, I will run this to get the new data:Insert into my_new_personselect a.person_GUI, xyzfrom my_old_person a join my_new_person bon a.person_GUI=b.person_GUIwhere not exists (Select person_GUI from my_old_person)How do I implement this person_ID to make it unique and populate correctly in above xyz?Thanks! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-31 : 15:35:20
|
You could make the Person_ID an int and make it an identity colum like so:CREATE TABLE my_new_person( person_GUI VARCHAR(255) NOT NULL , Person_ID NVARCHAR(20) NOT NULL IDENTITY(1,1)) I didn't quite follow the script that you want to run at the beginning of your nightly ETL. Is the goal to insert any new persons that are in the my_old_person table to my_new_Person_table? If so, and if you do the IDENTITY column that I proposed above, you would do this:INSERT INTO my_new_person (person_GUI)SELECT a.person_GUIFROM my_old_person aWHERE NOT EXISTS ( SELECT person_GUI FROM my_new_person ) |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-07-31 : 15:59:30
|
Great, thanks!Identity! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-31 : 16:05:03
|
You are welcome! Unfortunately, the update script that I posted is just plain wrong. You need this:INSERT INTO my_new_person (person_GUI)SELECT a.person_GUIFROM my_old_person aWHERE NOT EXISTS ( SELECT * FROM my_new_person b WHERE b.person_GUI = a.Person_GUI ) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 03:12:33
|
AlsoINSERT INTO my_new_person (person_GUI)SELECT a.person_GUIFROM my_old_person aEXCEPTSELECT b.person_GUIFROM my_new_person b ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-08-05 : 11:49:18
|
Actually, I implemented this one:INSERT INTO my_new_person (person_GUI)SELECT a.person_GUIFROM my_old_person aLEFT JOIN my_new_person b on a.GUI=b.GUIWhere b.GUI is nullNeverthless, thanks for both of you! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 11:52:36
|
That is just as good - probably better performing as well, although I am making a wild guess there. Regardless, now you know at least 3 ways to skin that cat! |
|
|
|
|
|
|
|