Storing GUIDs as Variables for future use

Question

Storing GUIDs as Variables for future use

James-283980

SSC Rookie

Points: 26
More actions
December 7, 2005 at 10:37 am

#167204

Hi All,
I am very new to the SQL world and I have a very straight forward question (i think) but it is driving me absolutely bonkers!
ok. I have a database with 2 tables in it,
accounts - holds a primary key called accountid which is a GUID
contacts - holds a primary key called contactid (again a GUID) and the accountid foreign key
I need to update the accountid foreign key in the contacts table which means that i need to store the primary accountid key in a variable so that i can populate the foreign accountid key in the contacts table.
Please anyone - can you show me the code so that i can store the priamry key GUID so that I can use it else where in the database
Many, Many thanks in advance!!
Jim
Kindest Regards,
James
<'mailto:impose80@hotmail.com' class='authorlink' target='_blank'>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

More info would be useful.

Why do you need a GUID ? Are you synchronising this data across multiple sites, or exchanging it with other external entities ? If not, just use an int with the Identity property.

Second, what is the process causing this ? Is this a database transaction where a new account is being created and assigned to a Contact ? Can a Contact only be on 1 Account ?

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

I would suggest storing the GUID in a varibale prior to doing the insert. Taht way it is pre-stored for use later in the query. For example:

DECLARE @guid_value VARCHAR(255)

SET @guid_value = NEWID()

INSERT INTO TEST99 (GUID_ID,TEST)

VALUES(@guid_value, 1)

UPDATE TEST98

SET GUID_ID_FK = @guid_value

James-283980 SSC Rookie Points: 26 More actions · Answer 3

Thank you Addict & Journeyman,

I think i am understanding now - this is my code for declarations, set values and then doing a new insert into the accounts table.

Jounerneyman - are u saying that i pre-store the new guid accountid at the start like you showed me in above reply? (highlighted in red) My next step would be to insert a new set of records into the contacts table, one of which would be the @accountid variable (accountid primary key) from tblaccount.

declare @AccountID uniqueidentifier

declare @eMailAddress varchar(100)

declare @Created datetime

declare @LastUpdated datetime

declare @VendorProvidedName varchar(200)

declare @Password varchar(20)

set @AccountID = newid()

set @eMailAddress = 'jbloggs@somewhere.com'

set @Created = getdate()

set @LastUpdated = getdate()

set @VendorProvidedName = 'Bloggs Ltd'

insert into tblaccount (AccountID , eMailAddress , Created , LastUpdated , VSPVendorName , Password)

values (@AccountID , @eMailAddress , @Created , @LastUpdated , 'Bloggs Ltd' , 'baggyjeans')

sorry i know i am struggling to understand - an example based on my above code would be fantastic

cheers

Jim

Kindest Regards,

James
<'mailto:impose80@hotmail.com' class='authorlink' target='_blank'>

ramses2nd Hall of Fame Points: 3720 More actions · Answer 4

ramses2nd

Hall of Fame

Points: 3720

December 7, 2005 at 11:46 am

#608238

That's the approach I have always used.

James-283980 SSC Rookie Points: 26 More actions · Answer 5

oh ok - following on from that, if i then write the following code to insert the accountid into the contacts table do you know why i get a foreign key conflict error?

(adding a @ContactID variable to declarations & set)

insert into tblcontact (ContactID , AccountID)

values (@ContactID , @AccountID)

thanks again!

Jim

Kindest Regards,

James
<'mailto:impose80@hotmail.com' class='authorlink' target='_blank'>

James-283980 SSC Rookie Points: 26 More actions · Answer 6

Hi there,

I have now resolved my issues with storing variables - many thanks to both Journeyman and Addict for all your help!

until next time!

all the best

James

Kindest Regards,

James
<'mailto:impose80@hotmail.com' class='authorlink' target='_blank'>