Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Generating Sequence Number....

    Hi,
    I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.


    Col1
    ----
    1
    1
    1
    1
    2
    2
    3
    3
    3
    3
    3
    4
    5
    6
    6
    7

    And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........

    Col1 Col2
    ---- -----
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    3 1
    3 2
    3 3
    3 4
    3 5
    4 1
    5 1
    6 1
    6 2
    7 1


    Thanks In Advance,
    Rahul Jha

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method
    Code:
    DECLARE @col1 int
    DECLARE @col2 int
    DECLARE @id   int
    DECLARE @i    int
    DECLARE @x    int
    DECLARE @myTable table (
        id   int identity(1,1)
      , col1 int
      , col2 int
      )
    
    SET NOCOUNT ON
    
    INSERT INTO @myTable(col1) VALUES(1)
    INSERT INTO @myTable(col1) VALUES(1)
    INSERT INTO @myTable(col1) VALUES(1)
    INSERT INTO @myTable(col1) VALUES(1)
    INSERT INTO @myTable(col1) VALUES(2)
    INSERT INTO @myTable(col1) VALUES(2)
    INSERT INTO @myTable(col1) VALUES(3)
    INSERT INTO @myTable(col1) VALUES(3)
    INSERT INTO @myTable(col1) VALUES(3)
    INSERT INTO @myTable(col1) VALUES(3)
    INSERT INTO @myTable(col1) VALUES(3)
    INSERT INTO @myTable(col1) VALUES(4)
    INSERT INTO @myTable(col1) VALUES(5)
    INSERT INTO @myTable(col1) VALUES(6)
    INSERT INTO @myTable(col1) VALUES(6)
    INSERT INTO @myTable(col1) VALUES(7)
    
    SET @i = 1
    SET @x = 1
    
    DECLARE theIckyCursor cursor
      FOR
    SELECT id
         , col1
    FROM   @myTable
    ORDER
        BY col1 ASC
    
    OPEN theIckyCursor
    
    FETCH NEXT FROM theIckyCursor INTO @id, @col1
    
    WHILE @@Fetch_Status <> -1 BEGIN
      IF @x <> @col1 BEGIN
        SET @x = @x + 1
        SET @i = 1
      END
    
        UPDATE @myTable
        SET    col2 = @i
        WHERE  id = @id
    
        SET @i = @i + 1
      FETCH NEXT FROM theIckyCursor INTO @id, @col1
    END
    
    CLOSE theIckyCursor
    DEALLOCATE theIckyCursor
    
    SELECT col1
         , col2
    FROM   @myTable
    
    SET NOCOUNT OFF
    You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

    (Basically, what you're trying to do sounds very wrong!)
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

    export your data to excel, use a macro to apply the sequence numbers

    otherwise, tell us your real situation, not this fantasy of only one column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, let's go back to the real world scenario.
    Can you provide proper column headings etc and describe WHY you want to add a sequence number?
    If you explain your justification for this design; perhaps we can suggest a better solution
    EDIT: Sniped!
    George
    Home | Blog

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

    Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.



    Thanks,
    Rahul Jha

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you use an ORDER BY to get the rows into the necessary sequence for numbering them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1

  9. #9
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    row_number???

    Can you make it more clear?



    Thanks,
    Rahul Jha

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    pdreyer's solution actually works. I have learned something new today

  12. #12
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

    I'll be obliged if any one let me know on the query that pdreyer has written.



    Thanks,
    Rahul Jha

  13. #13
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.



    Thanks,
    Rahul Jha

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please answer the question in post #7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This one ought to leave a mark!
    Code:
    SELECT 1 AS ick
      INTO #junque
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 3
    UNION ALL SELECT 3
    UNION ALL SELECT 3
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    
    SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
       INTO #foo
       FROM #junque
    
    UPDATE #foo
       SET bat = (SELECT Count(*)
          FROM #foo AS baz
          WHERE  baz.ick = #foo.ick
             AND baz.bar <= #foo.bar)
    
    SELECT ick, bat
       FROM #foo
    
    DROP TABLE #foo
    DROP TABLE #junque
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •