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

[Scotter]

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

--
Scotter

[Aaron [SQL Server MVP]]

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" <spam@spam.com> wrote in message
news:A6nEc.37113$w3.6637@fe2.texas.rr.com...

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

[Scotter]

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.xoc@dnartreb.noraa> wrote in message
news:eWMRq8jXEHA.2364@TK2MSFTNGP12.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" <spam@spam.com> wrote in message
> news:A6nEc.37113$w3.6637@fe2.texas.rr.com...

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

>
>

[Scotter]

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


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eWMRq8jXEHA.2364@TK2MSFTNGP12.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" <spam@spam.com> wrote in message
> news:A6nEc.37113$w3.6637@fe2.texas.rr.com...

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

>
>

[Aaron [SQL Server MVP]]

> 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]]

> 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]

[Scotter]

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]

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.

[Aaron [SQL Server MVP]]

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" <spam@spam.com> wrote in message
news:xArEc.21553$OX2.17844@fe2.texas.rr.com...

> Lemme see if I have the facts straight.