Insert Unique Primary Key using NewID in SQL Server

Last Reply 4 days ago By pandeyism

Posted 5 days ago

How to make a table with newID() as set as primary key of a table

i used a column and if a add 50 student then 50 coupan is gernate and add into table

but i add again 10 student then 10 new coupan is generated and add in table

mens used and a no of student at a any time so i want a coupon code generated which is primary key becouse no coupan is repeated it chance of collision.

i used this 

DECLARE @Table TABLE (Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY)
DECLARE @counter INT
SET @counter = 1
WHILE (@counter <= 1000)
BEGIN
INSERT INTO @Table VALUES(NEWID())
SET @counter = @counter + 1
END
SELECT LEFT(CONVERT(VARCHAR(MAX),Id),8) + RIGHT(CONVERT(VARCHAR(MAX),Id),6) Id FROM @Table

but what is my problem 

i create a table of student 

create table tbl_org_student
(
id int identity(1,1),
vno int,
vdate date,
orgid int ,
stuid int,
couponno nvarchar(10),
mobile varchar(12),
status char(1),
f1 nvarchar(max),
f2 nvarchar(max),
f3 nvarchar(max),
f4 nvarchar(max),
)
go

but i want 

create table tbl_org_student
(
id NEWID() as Primary Key,
vno int,
vdate date,
orgid int ,
stuid int,
couponno nvarchar(10),
mobile varchar(12),
status char(1),
f1 nvarchar(max),
f2 nvarchar(max),
f3 nvarchar(max),
f4 nvarchar(max),
)
go

 

You are viewing reply posted by: pandeyism 4 days ago.
Posted 4 days ago

Hi SajidHussa,

Refer below sample query.

SQL

DECLARE @tbl_org_student TABLE (Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY)
DECLARE @counter INT
DECLARE @NewId VARCHAR(50)
SET @counter = 1
WHILE (@counter <= 5)
BEGIN
	SET @NewId = (SELECT NEWID())
	IF NOT EXISTS(SELECT id FROM tbl_org_student WHERE id = @NewId)
	BEGIN
		INSERT INTO @tbl_org_student VALUES(@NewId)
		SET @counter = @counter + 1
	END
END
SELECT LEFT(CONVERT(VARCHAR(MAX),Id),8) + RIGHT(CONVERT(VARCHAR(MAX),Id),6) Id FROM @tbl_org_student
INSERT INTO tbl_org_student SELECT Id FROM @tbl_org_student
SELECT * FROM tbl_org_student