Copy record to same table

[zemp]

SQL Server 2000 SP4

Is there an easy method to copy or duplicate a record witrh in the same table. The only difference would be a new PK identity value.

I have a table that stores both Bill to and Ship to addresses. When a client enters a new bill to address I would like to give them the option of seeding a new ship to with the bill to address information.

zemp

 

[AlexCuse]

I think you should have 2 separate tables, possibly linked with a trigger?

Anyway, I suppose you could do something like this:

insert into myTable (PK, a, b, c)
select NewPKVal, a, b, c
from myTable
where PK = OldPKVal

Does this make sense?

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Alex, your idea of two separate tables with a trigger sounds interesting, How wouldm that work?

zemp

 

[AlexCuse]

Basically, you could have two tables (one with billing info, one with shipping). BOth would be tied to the user's unique PK value.

I am not sure that a trigger or doing updates only through SP's would be the best idea, but you could devise a way that IF shipping and billing addresses are the same, then an update on one will cause update of the other. One idea is to have a column in your billing address table called 'IsShipping', and write your trigger so that the update will only take place if BillingAddress is updated AND BillingAddress = ShippingAddress. Here is an idea (not tested). Let me know if you have any question.

[COLOR=blue]CREATE[/color] [COLOR=blue]TRIGGER[/color] TestUpdate [COLOR=blue]ON[/color] [TriggerTest]
[COLOR=blue]FOR[/color] [COLOR=blue]UPDATE[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
[COLOR=blue]update[/color] a
[COLOR=blue]set[/color] a.State = b.State
, a.City = b.City
, a.fName = b.fName
, a.lName = b.lName
[COLOR=blue]from[/color] ShippingAddress a 
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] BillingAddress b
[COLOR=blue]on[/color] a.PK = b.PK
and b.IsShipping = 1
and
(a.State <> b.State OR
a.City <> b.City OR
a.fName <> b.fName OR
a.lName <> b.lName)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Sorry zemp, first line should've read like this:

CREATE TRIGGER TestUpdate ON [BillingAddress]

Sorry for the mix-up

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Thanks Alex, I'll look into further.

zemp

 

[AlexCuse]

Sorry again zemp, forgot to show how to create the insert trigger (this is the one you'll need when creating a new record)

[COLOR=blue]CREATE[/color] [COLOR=blue]TRIGGER[/color] TestInsert [COLOR=blue]ON[/color] [BillingAddress]
[COLOR=blue]FOR[/color] [COLOR=blue]INSERT[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] ShippingAddress
[COLOR=blue]select[/color] State, City, Fname, Lname
[COLOR=blue]from[/color] inserted
[COLOR=blue]where[/color] IsShipping = 1

[COLOR=blue]END[/color]

Hope it help,

Alex

Ignorance of certain subjects is a great part of wisdom