2

I need to copy content from one table to itself and related tables... Let me schematize the problem. Let's say I have two tables:

Order
OrderID : int
CustomerID : int
OrderName : nvarchar(32)

OrderItem
OrderItemID : int
OrderID : int
Quantity : int

With the PK being autoincremental.

Let's say I want to duplicate the content of one customer to another. How do I do that efficiently?

The problem are the PKs. I would need to map the values of OrderIDs from the original set of data to the copy in order to create proper references in OrderItem. If I just select-Insert, I won't be able to create that map.

Suggestions?

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Vincent-Philippe Lauzon
  • 759
  • 2
  • 5
  • 20

4 Answers4

1

For duplicating one parent and many children with identities as the keys, I think the OUTPUT clause can make things pretty clean (SqlFiddle here):

-- Make a duplicate of parent 1, including children

-- Setup some test data
create table Parents (
      ID int not null primary key identity
    , Col1 varchar(10) not null
    , Col2 varchar(10) not null
)
insert into Parents (Col1, Col2) select 'A', 'B'
insert into Parents (Col1, Col2) select 'C', 'D'
insert into Parents (Col1, Col2) select 'E', 'F'

create table Children (
      ID int not null primary key identity
    , ParentID int not null references Parents (ID)
    , Col1 varchar(10) not null
    , Col2 varchar(10) not null
)
insert into Children (ParentID, Col1, Col2) select 1, 'g', 'h'
insert into Children (ParentID, Col1, Col2) select 1, 'i', 'j'
insert into Children (ParentID, Col1, Col2) select 2, 'k', 'l'
insert into Children (ParentID, Col1, Col2) select 3, 'm', 'n'

-- Get one parent to copy
declare @oldID int = 1

-- Create a place to store new ParentID
declare @newID table (
    ID int not null primary key
)

-- Create new parent
insert into Parents (Col1, Col2)
output inserted.ID into @newID -- Capturing the new ParentID
select Col1, Col2
from Parents
where ID = @oldID -- Only one parent

-- Create new children using the new ParentID
insert into Children (ParentID, Col1, Col2)
select n.ID, c.Col1, c.Col2
from Children c
    cross join @newID n
where c.ParentID = @oldID -- Only one parent

-- Show some output
select * from Parents
select * from Children
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • Bingo! That's what I was looking for to construct the mapping on the fly. Never heard of this 'output clause' before. Never seen the SQL fiddle either! Thanks! – Vincent-Philippe Lauzon Feb 01 '13 at 20:01
  • Glad to hear. Please upvote and/or mark as answer to help future visitors. Also, @MikaelEriksson has a great link to work with multiple parents. – Tim Lehner Feb 01 '13 at 20:05
0

Do you have to have the primary keys from table A as primaries in Table B? If not you can do a select statement with an insert into. Primary Key's are usually int's that start from an ever increasing seed (identity). Going around this and declaring an insert of this same data problematically has the disadvantage of someone thinking this is a distinct key set on this table and not a 'relationship' or foreign key value.

You can Select Primary Key's for inserts into other tables, just not themselves.... UNLESS you set the 'identity insert on' hint. Do not do this unless you know what this does as you can create more problems than it's worth if you don't understand the ramifications.

I would just do the ole:

insert into TableB select * from TableA where (criteria)

Simple example (This assumes SQL Server 2008 or higher). My bad I did not see you did not list TSQL framework. Not sure if this will run on Oracle or MySql.

declare @Order Table ( OrderID int identity primary key, person varchar(8));

insert into @Order values ('Brett'),('John'),('Peter');

declare @OrderItem Table (orderItemID int identity primary key, OrderID int, OrderInfo varchar(16));

insert into @OrderItem
select 
    OrderID  -- I can insert a primary key just fine
,   person + 'Stuff'
from @Order

select *
from @Order

Select *
from @OrderItem
djangojazz
  • 14,131
  • 10
  • 56
  • 94
0
  • Add an extra helper column to Order called OldOrderID
  • Copy all the Order's from the @OldCustomerID to the @NewCustomerID
  • Copy all of the OrderItems using the OldOrderID column to help make the relation

  • Remove the extra helper column from Order

    ALTER TABLE Order ADD OldOrderID INT NULL

    INSERT INTO Order (CustomerID, OrderName, OldOrderID) SELECT @NewCustomerID, OrderName, OrderID FROM Order WHERE CustomerID = @OldCustomerID

    INSERT INTO OrderItem (OrderID, Quantity) SELECT o.OrderID, i.Quantity FROM Order o INNER JOIN OrderItem i ON o.OldOrderID = i.OrderID WHERE o.CustomerID = @NewCustomerID

    UPDATE Order SET OldOrderID = null WHERE OldOrderID IS NOT NULL

    ALTER TABLE Order DROP COLUMN OldOrderID

Louis Ricci
  • 20,804
  • 5
  • 48
  • 62
  • I forgot to mention it wasn't a once-off but a 'normal' (although a bit rare) event in an application. I could leave the column there but it's a bit poluting on the schema. – Vincent-Philippe Lauzon Feb 01 '13 at 20:02
  • @Vincent-Philippe Lauzon - The code in the answer DROPs (removes) the extra column. You could use a temp table instead of altering the table, but this is the simplest solution. – Louis Ricci Feb 01 '13 at 20:05
0

IF the OrderName is unique per customer, you could simply do:

INSERT INTO [Order] ([CustomerID], [OrderName])
  SELECT
    2 AS [CustomerID],
    [OrderName]
  FROM [Order]
  WHERE [CustomerID] = 1

INSERT INTO [OrderItem] ([OrderID], [Quantity])
  SELECT
    [o2].[OrderID],
    [oi1].[Quantity]
  FROM [OrderItem] [oi1]
  INNER JOIN [Order] [o1] ON [oi1].[OrderID] = [o1].[OrderID]
  INNER JOIN [Order] [o2] ON [o1].[OrderName] = [o2].[OrderName]
  WHERE [o1].[CustomerID] = 1 AND [o2].[CustomerID] = 2

Otherwise, you will have to use a temporary table or alter the existing Order table like @LastCoder suggested.

Jacco
  • 3,251
  • 1
  • 19
  • 29