Professional Web Applications Themes

MS Access SQL: duplicate record... ?SELECT INTO....? - ASP Database

What syntax would you use to dupe a record? Thanks! -- Scotter...

Sponsored Links
  1. #1

    Default MS Access SQL: duplicate record... ?SELECT INTO....?

    What syntax would you use to dupe a record?
    Thanks!

    --
    Scotter


    Sponsored Links
    Scotter Guest

  2. #2

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    Why would you want to duplicate a row? (The fact that you call it a record
    gives some hint, at least.)

    We have primary keys for a reason.

    INSERT table(col1, col2, col3)
    SELECT col1, col2, col3
    FROM table
    WHERE ...

    Note that, since you allow duplicate rows, the SELECT portion may return
    more than one row, since you have no way to identify a single row (remember
    what I said about primary keys a moment ago?). However many rows match the
    SELECT statement, you will now double them.

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)





    "Scotter" <spamspam.com> wrote in message
    news:A6nEc.37113$w3.6637fe2.texas.rr.com...
    > What syntax would you use to dupe a record?
    > Thanks!

    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    Thanks for the info.
    Trust me I have a good reason to want to dupe.
    I *am* changing data in *one* field (so the resulting record is not
    *identical*) when I do the dupe and the method you gave me allows this.
    Thanks.
    Imagine a table called "pages" that is used as a back end for a web site.
    Imagine you are the admin and you want to create a new page but you don't
    want to start from scratch; you want to use another page you created
    yesterday and then modify it. So you want to *dupe* a row or record or
    whatever you want to call it.
    Regarding your assertion that I may end up duping many rows, I would like to
    explore this.
    What if I have a autonumber Primary Key in my pages table called "ID"?
    Can't I just do this:
    INSERT table(col2, col3)
    SELECT col2, col3
    FROM table
    WHERE ID=5
    note col2 and col3 are not the ID field. I left it out except for in the
    where clause.




    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:eWMRq8jXEHA.2364TK2MSFTNGP12.phx.gbl...
    > Why would you want to duplicate a row? (The fact that you call it a
    record
    > gives some hint, at least.)
    >
    > We have primary keys for a reason.
    >
    > INSERT table(col1, col2, col3)
    > SELECT col1, col2, col3
    > FROM table
    > WHERE ...
    >
    > Note that, since you allow duplicate rows, the SELECT portion may return
    > more than one row, since you have no way to identify a single row
    (remember
    > what I said about primary keys a moment ago?). However many rows match
    the
    > SELECT statement, you will now double them.
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >
    >
    >
    >
    > "Scotter" <spamspam.com> wrote in message
    > news:A6nEc.37113$w3.6637fe2.texas.rr.com...
    > > What syntax would you use to dupe a record?
    > > Thanks!
    >
    >

    Scotter Guest

  4. #4

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    Maybe you meant "INSERT INTO pages(col2,col3) SELECT ...."
    instead of "INSERT pages(col2,col3) SELECT..."?


    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:eWMRq8jXEHA.2364TK2MSFTNGP12.phx.gbl...
    > Why would you want to duplicate a row? (The fact that you call it a
    record
    > gives some hint, at least.)
    >
    > We have primary keys for a reason.
    >
    > INSERT table(col1, col2, col3)
    > SELECT col1, col2, col3
    > FROM table
    > WHERE ...
    >
    > Note that, since you allow duplicate rows, the SELECT portion may return
    > more than one row, since you have no way to identify a single row
    (remember
    > what I said about primary keys a moment ago?). However many rows match
    the
    > SELECT statement, you will now double them.
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >
    >
    >
    >
    > "Scotter" <spamspam.com> wrote in message
    > news:A6nEc.37113$w3.6637fe2.texas.rr.com...
    > > What syntax would you use to dupe a record?
    > > Thanks!
    >
    >

    Scotter Guest

  5. #5

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    > Maybe you meant "INSERT INTO pages(col2,col3) SELECT ...."
    > instead of "INSERT pages(col2,col3) SELECT..."?
    Maybe. Though, the INTO keyword is optional in the standard, and any decent
    database won't complain when it's missing.


    Aaron [SQL Server MVP] Guest

  6. #6

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    > What if I have a autonumber Primary Key in my pages table called "ID"?

    Then you probably chose an autonumber as a primary key for the wrong
    reasons. ;-)

    [url]http://www.aspfaq.com/2504[/url]


    Aaron [SQL Server MVP] Guest

  7. #7

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    Lemme see if I have the facts straight.

    Aaron says, "Why would you want to duplicate a row? (The fact that you call
    it a record gives some hint, at least.)"
    (ha ha ha)
    Scott replied with a very good reason why the application he has written has
    a need for this.
    Scott wonders why the little "jab" about "the fact that you call it a
    record..."? And yes, Aaron, I understand that maybe some people who consider
    themselves "elite database programmers" or "SQL Server MVP" wouldn't be
    caught dead calling a "row" a "record!" What a faux pax! And I appreciate
    you caring so much as to, in a sarcastic way, point out the correct db
    etiquette.

    Aaron assumes Scott is not using primary keys. He is wrong. Scott corrects
    him and shows good reason why he is using an autonumber primary key in this
    situation. Building on the faulty assumption, Aaron tells Scott this row
    dupe will produce copies of however many rows match the select statement.
    Yes, Aaron could have humbly acknowledged his faulty assumption but ... hey
    we have no expectations beyond a hope someone (thank you, Aaron) would help.
    If I blew a tire while on the road in the middle of the desert and a man
    stopped to help, would I get on his case for being rude or having stinky
    breath? Nope. Well... ok.. just a little.

    Scott thanks Aaron profusely for his help, even though the syntax provided
    was incorrect for MS Access and even though Aaron provided this inaccurate
    guidance (leaving out "INTO" for MS Access causes syntax error) with "jabs"
    at Scott's experience or knowledge or intelligence or database design
    strategy when these qualities are all relative and let's face it, you really
    can't know these things about a person based on a simple question, even if
    you feel like you are a god and have a deep desire to prove it. See "MS
    Access" in subject line. Yeah we all know SQL Server is superior. I bow down
    to the gods of SQL Server. How's that?

    Aaron tells Scott he "probably chose autonumber as primary key for wrong
    reason," which means Aaron assumes more knowledge of the needs of the
    application than the author of said application.

    Finally, Aaron defends his syntax error by denegrating the quality of MS
    Access, the database that the original question was regarding, with "the
    INTO keyword is optional in the standard, and any decent database won't
    complain when it is missing." The quality/choice of db application is not
    the point of the original question, is it?

    Thanks for tuning in. And yeah, even though you seem to have some opinions
    about my choices of database software, how I structure my tables, and why I
    would want to dupe a record- excuse me- a row, I do appreciate, Aaron, your
    help. Thanks for giving me an excuse to dodge my work for a bit and write
    this novel.


    Scotter Guest

  8. #8

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    I got curious and looked around this newsgroup and I see Aaron does help a
    lot of people with a lot of good info so I apologize for my little rant.
    I think I'm going to put a link to aspfaq on my web site. I'm glad to see
    people wanting to create resources such as this for helping fellow
    engineers.


    Scotter Guest

  9. #9

    Default Re: MS Access SQL: duplicate record... ?SELECT INTO....?

    You sure complain a lot, considering I was the only one offering to help
    you. Guess I won't make that mistake again.

    *PLONK*

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)




    "Scotter" <spamspam.com> wrote in message
    news:xArEc.21553$OX2.17844fe2.texas.rr.com...
    > Lemme see if I have the facts straight.

    Aaron [SQL Server MVP] Guest

Similar Threads

  1. Duplicate Record Inserts
    By Ren0420 in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 20th, 05:48 PM
  2. Select DIV and duplicate it
    By naked_ollie in forum Macromedia Contribute General Discussion
    Replies: 2
    Last Post: August 10th, 12:02 PM
  3. Duplicate record insertion
    By force4ormore in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 19th, 11:00 AM
  4. select all the duplicate records
    By atse in forum ASP
    Replies: 9
    Last Post: October 19th, 06:59 PM
  5. Duplicate a record in a table from a Form.
    By Jamie Perry in forum Microsoft Access
    Replies: 4
    Last Post: August 7th, 08:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139