Copy content in TSQL

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 

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 

• 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

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.