Trying to copy a row in a table

13 Jul 2006 4:11 PM

Taliesinson

I need to take a row in a table, and duplicate it in that same table, while
changing the values of two columns. Can anyone give a suggestion of the
easiest way to do this?

Currently I am trying to dump the data to a temporary table, changing the
values of the two columns, then using INSERT to put the modified row back
into the table. However, this is failing because of a TIMESTAMP column. I
could bypass the TIMESTAMP column by listing out all the columns that I do
want to insert, however, there are over 200, and the table itselfs represents
a template in a proprietary program that users may be modifying. So if
someone added a field to the template, I would have to go back in and modify
the stored procedure.

Any suggestions?

13 Jul 2006 4:30 PM

Roy Harvey

Your INSERT is going to have to include all the individual columns,
there really isn't any way around it. There is no need to use a temp
table though. Write a SELECT against the table that returns a row
that looks is close to what you want to add, with the changes you need
to make, and use that for the INSERT:

INSERT tablename(<columnlist>)
SELECT <column and expression list>
FROM tablename
WHERE <criteria to pick that one row>

You can save some typing by taking advantage of Query Analyzer. Find
the table in the tree on the left and right click, you can script it
out as a SELECT or INSERT with all the columns listed.

Roy Harvey
Beacon Falls, CT

On Thu, 13 Jul 2006 09:11:02 -0700, Taliesinson
<Taliesin***@discussions.microsoft.com> wrote:

Show quote

>I need to take a row in a table, and duplicate it in that same table, while
>changing the values of two columns. Can anyone give a suggestion of the
>easiest way to do this?
>
>Currently I am trying to dump the data to a temporary table, changing the
>values of the two columns, then using INSERT to put the modified row back
>into the table. However, this is failing because of a TIMESTAMP column. I
>could bypass the TIMESTAMP column by listing out all the columns that I do
>want to insert, however, there are over 200, and the table itselfs represents
>a template in a proprietary program that users may be modifying. So if
>someone added a field to the template, I would have to go back in and modify
>the stored procedure.
>
>Any suggestions?

13 Jul 2006 5:27 PM

Alexander Kuznetsov

Why would you want to duplicate redundant information? It sounds like
you could benefit from a better database design.

13 Jul 2006 5:32 PM

Arnie Rowland

If you are successful bringing the data into a #Temp table, and want to
continue down that path, you could change the timestamp column in the #Temp
table to NULL -BEFORE you INSERT it back to the parent table.

Timestamp is a system controlled value. You cannot insert a value into a
timestamp column.

I hope that this helps.

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."

Show quote

"Taliesinson" <Taliesin***@discussions.microsoft.com> wrote in message
news:88535A0C-8CF2-4CD0-A657-CD7CE243F252@microsoft.com...
>I need to take a row in a table, and duplicate it in that same table, while
> changing the values of two columns. Can anyone give a suggestion of the
> easiest way to do this?
>
> Currently I am trying to dump the data to a temporary table, changing the
> values of the two columns, then using INSERT to put the modified row back
> into the table. However, this is failing because of a TIMESTAMP column.
> I
> could bypass the TIMESTAMP column by listing out all the columns that I do
> want to insert, however, there are over 200, and the table itselfs
> represents
> a template in a proprietary program that users may be modifying. So if
> someone added a field to the template, I would have to go back in and
> modify
> the stored procedure.
>
> Any suggestions?