how to create a GUID for the following case !!

Question

Post reply

how to create a GUID for the following case !!

koolme_85

Ten Centuries

Points: 1153
More actions
November 28, 2011 at 5:55 pm

#263552

i have the following table
table1
customer inventory item billingzip
tom, furniture, chair, 17603,
tom, electronics, TV, 91605,
john, furniture, table 75038,
john, sport, ball, 75055,
tim, cosmetics deospray, 17569,
customer inventory item billingzip are the columns and below them are the records
i would like a system generated guid and the there must be only one guid per customer
for example
for both the records for tom the guid should be "30FDB27E-6ADF-4374-A37C-D242C3FF5C0F"
for both the records for john the guid should be "1235ADD4-F60F-4B75-A951-24AE8ED7C2AF"
and for tim guid should be "F7050364-EB8E-4312-8B6A-F27EC009F5AE"
so total 3 distinct guids for 5 records
can any one help me on this one ?
thanks
regards
greg

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

the newid() function and the datatype uniqueidentifier for the column data type is what you are looking for;

you can make it the default value of a table, but i would recommend integer and identity over uniqueidentifer columns.

you need to find the preexisting guid for "tom" before you can use it in a different row... name is not at all unique..i'd consider using an email address instead for the PK, and skip the uniqueidentifier altogether

select newid(),newid(),othervalues

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

koolme_85 Ten Centuries Points: 1153 More actions · Answer 2

koolme_85

Ten Centuries

Points: 1153

November 28, 2011 at 6:19 pm

#1413723

can you give me as example !!

Lowell SSC Guru Points: 323546 More actions · Answer 3

example of what? an insert with newid? the way i would do it?

you'll need to provide a lot more details, i think..what table DDL do you have so far? what are you trying to do? it seems like you really wanted to insert into three different tables...so i need more on what you are trying to accomplsish if you want a solid example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ant-Green SSC Guru Points: 113563 More actions · Answer 4

table with the customers which has a uniqueidentifer column which has a default of newid so each customer has a GUID

then have a foreign key constraint to table 1 which references the GUID, when you do the insert into table 1 select the guid from the customers table and feed it through

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

koolme_85 (11/28/2011)
so total 3 distinct guids for 5 records
can any one help me on this one ?
thanks
regards
greg

Guess based on your data.

BEGIN TRAN

--Sample data as show by the OP

DECLARE @TABLE AS TABLE (customer VARCHAR(4), inventory VARCHAR(11), item VARCHAR(9), billingzip INT,

--Added ID as the place for the GUID to go

ID UNIQUEIDENTIFIER NULL)

INSERT INTO @TABLE

SELECT 'tom', 'furniture', 'chair', 17603, NULL

UNION ALL SELECT 'tom', 'electronics', 'TV', 91605, NULL

UNION ALL SELECT 'john', 'furniture', 'table', 75038, NULL

UNION ALL SELECT 'john', 'sport', 'ball', 75055, NULL

UNION ALL SELECT 'tim', 'cosmetics', 'deospray', 17569, NULL

--First, let's generate GUIDs for each unqiue "customer"

SELECT customer AS cust, NEWID() AS ID

INTO #Temp

FROM @TABLE

GROUP BY customer

--Now update the sample data with the newly generated GUIDs

UPDATE a

SET a.ID = b.ID

FROM @TABLE a

INNER JOIN #Temp b ON cust = customer

--Check the result-set

SELECT * FROM @TABLE

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

koolme_85 Ten Centuries Points: 1153 More actions · Answer 6

thanks for all the help guys , i figured it out ,

i took all the distinct values of one column and put them in a temp table and added a ref column to that temp table and gave it a value "NEWID()" i this way i have distinct guid for each record and then i did a inner join with this temp table to my main table ... and it seemed i got what i needed