Copy current record

Hi There
Tablename "tblAppDetails"
Which includes the following fields
AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
varchar 100, AppAddress1 varchar 100
What I'd like are 2 SP's, the first to select the all the fields (other than
AppDate & AppTime) for the current record and the second to paste those
values onto another record in the same table (this is for moving someone fro
m
one appointment to another. The user sees one record to view and'll click a
button to copy, select a new record and click another button to paste) .
Is this something to do with Fetch?
Many thanks
PaulPaul in Harrow wrote:
> Hi There
> Tablename "tblAppDetails"
> Which includes the following fields
> AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
> varchar 100, AppAddress1 varchar 100
> What I'd like are 2 SP's, the first to select the all the fields
> (other than AppDate & AppTime) for the current record and the second
> to paste those values onto another record in the same table (this is
> for moving someone from one appointment to another. The user sees one
> record to view and'll click a button to copy, select a new record and
> click another button to paste) .
> Is this something to do with Fetch?
> Many thanks
> Paul
The PK in the table seems a little iffy. What prevents two records from
having the same date and time?
With that PK, why not just change the date and time to make the change.
I assume by "current record" you mean a row of data that a user is
editing. I'm not sure what you mean by "another record" because of the
PK.
Could you explain in a little more detail what data you want to move,
where it's going and if any other tables are involved in the process.
David Gugick
Imceda Software
www.imceda.com|||You don't need cursors and multiple SPs. A single UPDATE will do it:
UPDATE tblAppDetails
SET appname =
(SELECT appname
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1),
(SELECT appaddress1
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1)
WHERE appdate = @.ad2
AND apptime = @.at2
(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively.
Why are you using separate columns for date and time when SMALLDATETIME
stores both?
David Portas
SQL Server MVP
--|||Thanks both Davids
"David Portas" wrote:
"Why are you using separate columns for date and time when SMALLDATETIME
stores both?"
I know, but this is not my table and it's been pre-filled with 24 AppTimes
for each AppDate for the next five years!
"(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively."
Er, how? @.ad1 &@.at1 are from the current record but how does it know which
is the second record?|||In my query you need to specify the keys for both the source row and
the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
"second row"? You didn't specify how the target row for the change is
to be determined so I assumed you were supplying that information as
parameters in your SP. If you wanted something else then please give us
a full description of the problem: DDL, sample data INSERT statements
and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||David,
I'll do all this on Monday
Paul
"David Portas" wrote:

> In my query you need to specify the keys for both the source row and
> the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
> "second row"? You didn't specify how the target row for the change is
> to be determined so I assumed you were supplying that information as
> parameters in your SP. If you wanted something else then please give us
> a full description of the problem: DDL, sample data INSERT statements
> and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>