Cloning a record ?

[Evertjan.]

How do I clone a record, given it's ID,
to a new record copying all fields but the autoincrement ID field?

INSERT INTO myTbl (SELECT * FROM myTbl WHERE ID=17)

[This is more pseudocode than a real attempt]

I am using the Jet engine.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Aaron [SQL Server MVP]]

You'd have to name all the other columns.

However, why would you want to do this? How are you going to tell the new
row from the old? What is the purpose of repeating the data?

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




"Evertjan." <exjxw.hannivoort@interxnl.net> wrote in message
news:Xns9508D8131C5DFeejj99@194.109.133.29...

> How do I clone a record, given it's ID,
> to a new record copying all fields but the autoincrement ID field?
>
> INSERT INTO myTbl (SELECT * FROM myTbl WHERE ID=17)
>
> [This is more pseudocode than a real attempt]
>
> I am using the Jet engine.
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

[Evertjan.]

Aaron [SQL Server MVP] wrote on 14 jun 2004 in
microsoft.public.inetserver.asp.db:

> You'd have to name all the other columns.

I was afraid of that.

> However, why would you want to do this? How are you going to tell the
> new row from the old? What is the purpose of repeating the data?

I'm building a family tree database, where some couples had 12 children.
[and Indonesian names next to the original Chinese ones with inverted
sequence, or Jewish patronyms]

Cloning will have me change only the info where a record differs from the
original, which is not the family name and not the parent link values,
where most mistakes are made.

However, I found a way forcing the correction page UPDATING immediately on
a new INSTERTed empty record instead of it's usual UPDATE on its own
record. Simple [and perhaps dirty coding] but a great time saver. This page
is only accessible by/to? myself.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[IPT]

You may try this:

INSERT INTO myTbl
SELECT *
FROM myTbl
WHERE ID = 17

That code allows multiple rows to be inserted at 1 go.

> How do I clone a record, given it's ID,
> to a new record copying all fields but the autoincrement ID field?
>
> INSERT INTO myTbl (SELECT * FROM myTbl WHERE ID=17)
>
> [This is more pseudocode than a real attempt]
>
> I am using the Jet engine.

[Evertjan.]

IPT wrote on 20 jun 2004 in microsoft.public.inetserver.asp.db:

>> How do I clone a record, given it's ID,
>> to a new record copying all fields but the autoincrement ID field?
>>
>> INSERT INTO myTbl (SELECT * FROM myTbl WHERE ID=17)
>>
>> [This is more pseudocode than a real attempt]
>>
>> I am using the Jet engine.

>
> You may try this:
>
> INSERT INTO myTbl
> SELECT *
> FROM myTbl
> WHERE ID = 17
>
> That code allows multiple rows to be inserted at 1 go.

[please do not change to topposting]

This does not work with MS Jet engine.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> IPT wrote on 20 jun 2004 in microsoft.public.inetserver.asp.db:

>> You may try this:
>>
>> INSERT INTO myTbl
>> SELECT *
>> FROM myTbl
>> WHERE ID = 17
>>
>> That code allows multiple rows to be inserted at 1 go.

>
> This does not work with MS Jet engine.

I was all set to jump all over this until I realized what "does not work"
meant in the context of this thread. :-)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Evertjan.]

Bob Barrows [MVP] wrote on 20 jun 2004 in
microsoft.public.inetserver.asp.db:

> Evertjan. wrote:

>> IPT wrote on 20 jun 2004 in microsoft.public.inetserver.asp.db:

>>> You may try this:
>>>
>>> INSERT INTO myTbl
>>> SELECT *
>>> FROM myTbl
>>> WHERE ID = 17
>>>
>>> That code allows multiple rows to be inserted at 1 go.

>>
>> This does not work with MS Jet engine.

>
> I was all set to jump all over this until I realized what "does not
> work" meant in the context of this thread. :-)

Sorry, Bob!

It means "Does give an error" and "does not execute".

This is as I expected.

===============

btw, I prefer the word "record" to the word "row" in a database.
Row is a spreadsheet word.
A database has records and fields.
So I also detest the naming of getRows().
It should have been getRecords().

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> Bob Barrows [MVP] wrote on 20 jun 2004 in
> microsoft.public.inetserver.asp.db:

>> Evertjan. wrote:

>>> IPT wrote on 20 jun 2004 in microsoft.public.inetserver.asp.db:
>>>> You may try this:
>>>>
>>>> INSERT INTO myTbl
>>>> SELECT *
>>>> FROM myTbl
>>>> WHERE ID = 17
>>>>
>>>> That code allows multiple rows to be inserted at 1 go.
>>>
>>> This does not work with MS Jet engine.

>>
>> I was all set to jump all over this until I realized what "does not
>> work" meant in the context of this thread. :-)

>
> Sorry, Bob!
>
> It means "Does give an error" and "does not execute".
>

Well, that's just not right. That is perfectly legal syntax for Jet. I
thought you meant that it would not do what you wanted: insert values for
all the fields except the ID field. That sql statement should execute
without error in Jet. In fact, I just tested it. It works fine.

> This is as I expected.
>
> ===============
>
> btw, I prefer the word "record" to the word "row" in a database.
> Row is a spreadsheet word.
> A database has records and fields.

:-)
I tend to use both interchangeably. You should realize however, that
"column" and "row" are the correct terms to use when discussing relational
databases. In the strictest sense, they do have different meanings from
"record" and "field". The strict definitions of "record" and "field" make
them the incorrect words to use when discussing relational tables. But only
the most pedantic experts make an issue of it. They are inarguably correct,
but that does not make them any less pedantic in my opinion.

> So I also detest the naming of getRows().
> It should have been getRecords().

This, I do agree with. A recordset is a set of records, not rows.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Evertjan.]

Bob Barrows [MVP] wrote on 20 jun 2004 in
microsoft.public.inetserver.asp.db:

>> It means "Does give an error" and "does not execute".
>>

>
> Well, that's just not right. That is perfectly legal syntax for Jet. I
> thought you meant that it would not do what you wanted: insert values for
> all the fields except the ID field. That sql statement should execute
> without error in Jet. In fact, I just tested it. It works fine.
>

I will try again.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[IPT]

Programming is like sex. You make little mistake, you are gonna support it
for life.

They coded it getRow(), everyone gets to use it. Now, they need to maintain
it.

> > So I also detest the naming of getRows().
> > It should have been getRecords().

>
> This, I do agree with. A recordset is a set of records, not rows.