Unique Id

Question

User1979860870 posted

Hi

  I have below code . In UniqueId, i want Unique value should come. It can be Roll No. I dont want to set Column as Identity

INSERT INTO [tbl1] (Code,Name,UniqueId )
    (Select Code,Name,UniqueId from tbl2)

Answer 1

User753101303 posted

Hi,

Knowing the purpose of those 2 tables could help to understand why it can't be a copy of the source id or an identity column and understand which other option could fit your needs.

Assuming the uniqueid needs to be unique accross those two tables you could try https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15.
Sometimes it's easier to have a single table with an additional column rather than multiple tables with the same structure (for example a table for each month of data).

If it doesn't help please keep in mind it is easier to help if you explain first which problem you have.

Answer 2

User-939850651 posted

Hi jagjit saini,

According to your description, I guess:

In the UniqueId column in the tb2 table, there are multiple duplicate records, and there are also unique records that are not duplicated. What you expect is to find out the records that do not contain duplicates and insert them into the tb1 table.

If this is the case, have you tried to use group by ... having count(*)=1？

This is my test:

create table tb1(
Code varchar(50) default 'code',
Name varchar(50) default 'Name',
UniqueId int
)

insert into tb1 (UniqueId) values (1),(2),(2),(3),(4),(4),(5),(6),(6)

select Code,Name,UniqueId 
into tb2 
from tb1 
group by Code,Name,UniqueId 
having count(*)=1

select * from tb1
select * from tb2

Result:

If I misunderstood something, could you provide more details?

Best regards,

Xudong Peng

Answer 3

User-939850651 posted

Hi jagjit saini,

Have you solve your problem?