Results 1 to 3 of 3

Thread: one record

  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: one record

    hi guys,
    so i have a table which has at least 2 records that are almost similar except for 2 or 3 columns. i want to copy one of the records(just one) into another table. may you please help me with a script. NB the table has 11million plus records with the above mentioned discription. e. g
    col 1 col2 col3 col4
    a b c d
    a b n d

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT
      INTO table2
    SELECT col1
         , col2
         , col3
         , col4
      FROM table1
     WHERE col1 = 'a'
       AND col2 = 'b'
       AND col3 = 'n'
       AND col4 = 'd'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    I do not know what will be the performance, or is there another better way, but try something like that:

    Code:
    with CTE as
    (
        select 
            *, 
            ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY col3) as RowNum
        from MyTable
    )
    
    insert into MyOtherTable (col1, col2, col3, col4)
        select col1, col2, col3, col4 
        from CTE
        where RowNum = 1
    Hope this helps.

Posting Permissions

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