Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: how to CREATE duplicate rows

    this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition.

    using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id.

    but is there any one to perform this action just using sql?

    thanks,

    goran.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    insert your_table
    select * from your_table x
    inner join (
    select field_you_want_to_have_duplicates_on, cnt=count(*)
    from your_table group by field_you_want_to_have_duplicates_on
    having count(*) > 1) y
    on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think I just heard their head explode...

    They want to do it from asp...that's a pure sql solution (which is the fastest btw)

    Just loop through you're rs and do an insert for every iteration...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i will disagree
    if they truly wanted to do it from asp, why did they come to the sql forum????

    they want sql and they know it!! and there's no denying

    isnt that right rdjabarov?
    rd? rd... RD!
    well he HAD my back on this..

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    AAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRHHHHHHHH!!!

    I'm on 2 conf calls and typing here
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    hmmm ... i think you guys got me wrong, my current solution is implemented in asp using the same method that me and you have described. i was seeking a pure SQL solution.

    anyway, thanks for replying, i've tried adapting your sql statement to my tables but am not getting very far. let me explain a little further ...

    i have a table called 'tblWinesEnPrimeur' and want to make a duplicate of every row in this table that has a 'SectionID' of 21.

    thanks,
    goran.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.
    Very helpful....

    I'm betting you have an IDENTITY Column...ket's say Col1

    INSERT INTO tblWinesEnPrimeu (Col2, Col3, Col4, ect)
    SELECT Col2, Col3, Col4, ect)
    FROM tblWinesEnPrimeu
    WHERE SectionId = 21

    Without the DDL it's a guess...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using:
    PHP Code:
    INSERT INTO yourTable (collist)
       
    SELECT *
          
    FROM yourTable
          WHERE  21 
    sectionId 
    THis should massively confuse your data, and make the users very happy!

    -PatP

  10. #10
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    sorry guys, no luck with that either, it's on the client's server and we have no direct access to the database, also the website was orignally designed so that duplicates are not inserted by front-end users of the site.

    the sql threw up the same exception as before, simple 'Object required'. like i said it's all workin using the asp/sql combined solution, but the client prefers straight forward sql scripts rather than asp pages which can be run many times and inflate or alter the database.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I give up...I'm totally lost....

    Maybe if you post some (any) code, it might help....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll second that thought! I'd suggest that you use SQL Enterprise Mangler to script everything about that table, the DDL, the indicies, even the permissions. Given that and the nickel description that you posted above, I'll bet that one of us can work out the solution!

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    insert your_table
    select * from your_table x
    inner join (
    select field_you_want_to_have_duplicates_on, cnt=count(*)
    from your_table group by field_you_want_to_have_duplicates_on
    having count(*) > 1) y
    on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on
    where x.SectionID = 21
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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