NEWID() with data type varchar

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

SQL Server 2005 Forums

Transact-SQL (2005)

NEWID() with data type varchar

Author

Topic

jak3f
Starting Member

33 Posts

Posted - 2009-11-09 : 12:59:18

How can I randomly generate a PK if I insert all other fields expcept for the PK using a data type varchar?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-09 : 13:00:57

What would you like the PK format to be as an example? A GUID perhaps?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 13:01:31

you can use NEWID() to get a random GUID value if thats what you want.how do you want PK values to be?

jak3f
Starting Member

33 Posts

Posted - 2009-11-09 : 20:33:16

When I try to insert, insert fails because it says PK column cannot be null, but I cannot insert a newid() value... how do I do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-09 : 20:52:23

Store the NEWID() value into a variable and then use the variable in your INSERT.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 02:00:56

or have a default value of newid() to that column and omit that column during insertion

Madhivanan

Failing to plan is Planning to fail

jak3f
Starting Member

33 Posts

Posted - 2009-11-10 : 10:36:34

Ahhh I think I found my problem. I have a INSTEAD OF INSERT Trigger that is based upon CustomerID,
It is an upsert that is updating based on a different field, EFIN. And if there is no match because EFIN is null, then insert a new record, however I had written it for if CustomerID was not a field that was randomly genereated.
Can anyone help me rewrite this?
ALTER TRIGGER trg_duplicate_prevent
ON dbo.Customers
INSTEAD OF Insert
AS
IF EXISTS(SELECT EFIN FROM Customers WHERE EFIN IS NOT NULL)
BEGIN
UPDATE C SET
C.Name = i.Name
,C.Address = i.Address
,C.City = i.City
,C.State = i.State
,C.ZipCode = i.ZipCode
,C.Phone = i.Phone
,C.Email = i.Email
,C.EFIN = i.EFIN
,C.RegCode = i.RegCode
,C.AccountNumber = i.AccountNumber
,C.ChargeNumber = i.ChargeNumber
,C.ProductCode = i.ProductCode
,C.PurchaseDate = i.PurchaseDate
,C.PriorSoftware = i.PriorSoftware
,C.Bank = i.Bank
,C.BalanceDue = i.BalanceDue
,C.Renewed = i.Renewed
FROM inserted i JOIN Customers C
ON (C.EFIN = I.EFIN)
WHERE C.EFIN > 1
END
ELSE
BEGIN
Insert Customers SELECT i.CustomerID,
i.Name,
i.Address,
i.City,
i.State,
i.ZipCode,
i.Phone,
i.Email,
i.EFIN,
i.RegCode,
i.AccountNumber,
i.ChargeNumber,
i.ProductCode,
i.PurchaseDate,
i.PriorSoftware,
i.Bank,
i.BalanceDue,
i.Renewed
FROM inserted i
left outer join Customers c
on c.CustomerID = i.CustomerID
WHERE c.CustomerID is null
END

jak3f
Starting Member

33 Posts

Posted - 2009-11-10 : 10:45:24

Ok I changed the TRIGGER to this and it works:

ALTER TRIGGER trg_duplicate_prevent
ON dbo.Customers
INSTEAD OF Insert
AS
IF EXISTS(SELECT EFIN FROM Customers WHERE EFIN IS NOT NULL)
BEGIN
UPDATE C SET
C.Name = i.Name
,C.Address = i.Address
,C.City = i.City
,C.State = i.State
,C.ZipCode = i.ZipCode
,C.Phone = i.Phone
,C.Email = i.Email
,C.EFIN = i.EFIN
,C.RegCode = i.RegCode
,C.AccountNumber = i.AccountNumber
,C.ChargeNumber = i.ChargeNumber
,C.ProductCode = i.ProductCode
,C.PurchaseDate = i.PurchaseDate
,C.PriorSoftware = i.PriorSoftware
,C.Bank = i.Bank
,C.BalanceDue = i.BalanceDue
,C.Renewed = i.Renewed
FROM inserted i JOIN Customers C
ON (C.EFIN = I.EFIN)
WHERE C.EFIN > 1
END

but Now i need to insert records that do not exist too. hmm

jak3f
Starting Member

33 Posts

Posted - 2009-11-10 : 12:01:45

I was thinking maybe something like this would work? but it doesnt.
Insert Customers SELECT i.CustomerID,
i.Name,
i.Address,
i.City,
i.State,
i.ZipCode,
i.Phone,
i.Email,
i.EFIN,
i.RegCode,
i.AccountNumber,
i.ChargeNumber,
i.ProductCode,
i.PurchaseDate,
i.PriorSoftware,
i.Bank,
i.BalanceDue,
i.Renewed
FROM inserted i
WHERE i.CustomerID is null
END
Since in inserting i the customerId field will be null, and is supposed to be filled by the newid() method but this is not working.
I am not even sure what I am trying to accomplish is possible

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 13:03:02

instead of insert gets fired before insert so newid() wont have generated value by then

jak3f
Starting Member

33 Posts

Posted - 2009-11-10 : 13:48:51

Yeah thats what I am finding, can I get around that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-11 : 12:10:18

i didnt understand what you're trying to do can you explain?