locked
Unique Id RRS feed

  • 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)

    Friday, November 20, 2020 7:32 AM

All replies

  • 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.

    Friday, November 20, 2020 9:09 AM
  • 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

    Monday, November 23, 2020 6:15 AM
  • User-939850651 posted

    Hi jagjit saini,

    Have you solve your problem?

    Friday, November 27, 2020 7:14 AM