locked
insert Extra id number RRS feed

  • Question

  • Hi guys, i have 3 tables called Harddisk, Printers, Monitors. now i want to put them in one table called Products so when i import the data it clashes because of the Column ID every table has a same ID. now waht i want is to put a uniqe number in front of the Id number like this:

    Harddisks will have all the ID number start with 1 in front.

    Printers will have all the ID number start with 2 in front.

    Monitors will have all the ID number start with 3 in front.

    so then i can put them like a groups too.

    Some thing like:

    ID    Type

    212   Printer

    213 Printer

    214 Printer

    111 Harddisk

    113 harddisk

    301 Monitors

     

    please help

     

     

    Wednesday, April 20, 2011 12:56 PM

Answers

  • While other people have suggested ways to solve your problem, I'd urge you to rethink whether you really want to solve your underlying problem that way.  My advice is to let the primary key be the primary key, and use another column to categorize the type of item the row corresponds to.

    Some thoughts:

    1.  If you follow your plan, how will you insert new printers?  You're going to have to carefully set the primary key to the next higher number that starts with "2".  You'll need to do that calculation  each time you insert a new row.

    2.  What happens when you have more than 100 printers?  Will printer #101 be recorded as 2001?

    3.  You are digging a design hole here.  What happens if you add scanners to the hardware list?  No problem, you say.  Scanners will all start with "4".  Then you suddenly have a combination printer/scanner.  Where does that go?

    4.  Let's assume that you win the lottery tomorrow, and decide to retire to an island.  Would the "encoding" of the ID be obvious to the person who looks at the schema for your database?

    There aren't insurmountable, but it seems MUCH easier to just have a primary key, and then have a foreign key reference (perhaps through an intermediary table) to the attributes that you want to associated with that row.

    • Proposed as answer by Peja Tao Thursday, April 28, 2011 4:36 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 3, 2011 4:06 AM
    Wednesday, April 20, 2011 3:03 PM

All replies

  • create table #t (id  int not null identity(1,1),type varchar(10))

    insert into #t (type) select 'monitor'
    insert into #t(type)  select 'hard disk'

     

    select type,
    case when type='monitor' then 500+( select count(*) from #t t where type='monitor' and t.id<=#t.id)
         when type='hard disk' then 300+( select count(*) from #t t where type='hard disk' and t.id<=#t.id) end

    from #t

    insert into #t (type) select 'monitor'
    insert into #t(type)  select 'hard disk'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 20, 2011 1:15 PM
    Answerer
  • Hi,

    Another way

    declare @t table
    ( id int,
    type nvarchar(100))
    
    declare @h table
    ( id int,
    type nvarchar(100))
    
    declare @p table
    ( id int,
    type nvarchar(100))
    
    declare @m table
    ( id int,
    type nvarchar(100))
    
    
    insert into @h
    select 1, 'HardDisk1'
    union all
    select 2, 'HardDisk1'
    
    insert into @p
    select 1, 'Printer1'
    union all
    select 2, 'Printer2'
    
    insert into @m
    select 1, 'Monitor1'
    union all
    select 2, 'Monitor2'
    
    insert into @t
    select '1' + CAST (id as nvarchar(10)),type from @h
    union all
    select '2' + CAST (id as nvarchar(10)),type from @m
    union all
    select '2' + CAST (id as nvarchar(10)),type from @p
    
    select * from @t
    
    
    
    

    - Chintak (My Blog)

    Wednesday, April 20, 2011 1:21 PM
  • thx guys but dont worry about the type.

    i all ready have id's in the column all i need to put uniqe number in front of all of it some thing like.

    ID

    [1]02

    [1]03

    [1]04

    [2]05

    [2]06

    [3]07

    [3]08

    [3]09

    like it will be normal id insert but it will automaticly put [1], [2], [3] these kind of number in front so i will know 1 is harddisk, 2 is printer and so on...

    hit me back

    Wednesday, April 20, 2011 1:39 PM
  • and i have table called product type over there i can define that 1 is harddisk and 2 is printer so on.

    and please dont mind the [] as well its just to show the uniqe number that i want.

    i want a command so i can insert these uniqe number in my old tables and then take them all and put them in one place.

    coz every table has id's like this now:

    harddisk table                     printer table                                 monitors table

    id                                         id                                                id

    1                                          1                                                  1

    2                                          2                                                  2      

    3                                          3                                                  3

    4                                          4                                                  4

    and i want to insert like this:

    harddisk table                     printer table                                 monitors table

    id                                         id                                                id

    [1]1                                          [2]1                                                  [3]1

    [1]2                                          [2]2                                                  [3]2      

    [1]3                                          [2]3                                                  [3]3

    [1]4                                          [2]4                                                  [3]4

    ofcourse i don't want this [] writen with it :)

    please help

    Wednesday, April 20, 2011 1:48 PM
  • While other people have suggested ways to solve your problem, I'd urge you to rethink whether you really want to solve your underlying problem that way.  My advice is to let the primary key be the primary key, and use another column to categorize the type of item the row corresponds to.

    Some thoughts:

    1.  If you follow your plan, how will you insert new printers?  You're going to have to carefully set the primary key to the next higher number that starts with "2".  You'll need to do that calculation  each time you insert a new row.

    2.  What happens when you have more than 100 printers?  Will printer #101 be recorded as 2001?

    3.  You are digging a design hole here.  What happens if you add scanners to the hardware list?  No problem, you say.  Scanners will all start with "4".  Then you suddenly have a combination printer/scanner.  Where does that go?

    4.  Let's assume that you win the lottery tomorrow, and decide to retire to an island.  Would the "encoding" of the ID be obvious to the person who looks at the schema for your database?

    There aren't insurmountable, but it seems MUCH easier to just have a primary key, and then have a foreign key reference (perhaps through an intermediary table) to the attributes that you want to associated with that row.

    • Proposed as answer by Peja Tao Thursday, April 28, 2011 4:36 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 3, 2011 4:06 AM
    Wednesday, April 20, 2011 3:03 PM