Insert into Three Linking Tables


http://forums.asp.net/p/2102281/6076257.aspx?p=True&t=636078096225251552

  

  CREATE TABLE [dbo].[tCustomers] (
    [CustID]  INT        IDENTITY (1, 1) NOT NULL,
    [CustName] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([CustID] ASC)
);

CREATE TABLE [dbo].[tSalesOrders] (
    [SOID]         INT        IDENTITY (1, 1) NOT NULL,
    [CustID]       INT        NOT NULL,
    [SODescription] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([SOID] ASC)
);

CREATE TABLE [dbo].[tSalesItems] (
    [SIID]         INT        IDENTITY (1, 1) NOT NULL,
    [SOID]         INT        NOT NULL,
    [SIDescription] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([SIID] ASC)
);

 USE [mydb1]
GO
/****** Object:  StoredProcedure [dbo].[spCopyCustomer]    Script Date: 8/26/2016 2:03:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCopyCustomer]
    @CustID AS INT   /* CustID of the customer we want to copy */
AS
BEGIN

   --add an extra column
 IF COL_LENGTH('dbo.tSalesOrders','OlDSOID') IS NULL
 BEGIN
 ALTER TABLE dbo.tSalesOrders ADD OlDSOID INT;
 END;

DECLARE @NewCustID AS INT;
Create table #tmpCustID   (SOID int, OldCustID INT, OlDSOID2 INT); 

INSERT INTO tCustomers (CustName)
  SELECT CustName FROM tCustomers
    WHERE tCustomers.CustID = @CustID;

--Get the new inserted CustID
Select @NewCustID=scope_identity()

declare @sql nvarchar(4000)= N'INSERT INTO tSalesOrders (CustID, SODescription,OlDSOID)
OUTPUT  INSERTED.SOID,'+  Cast(@CustID as nvarchar(30))+',  INSERTED.OlDSOID INTO #tmpCustID(SOID, OldCustID, OlDSOID2)
SELECT '+ Cast(@NewCustID as nvarchar(30))  +', SODescription, SOID FROM tSalesOrders WHERE CustID = '+ Cast(@CustID as nvarchar(30))

 EXEC sp_executesql @sql 

INSERT tSalesItems (SOID, SIDescription)
Select  t.SOID, si.SIDescription
from #tmpCustID t join tSalesOrders so on t.OlDSOID2=so.SOID join tSalesItems si on si.SOID=so.SOID
WHERE t.OldCustID=@CustID

   --drop the extra column
 IF COL_LENGTH('dbo.tSalesOrders','OlDSOID') IS Not NULL
 BEGIN
 ALTER TABLE dbo.tSalesOrders Drop column OlDSOID;
 END;

End

--Another approach:

ALTER PROCEDURE [dbo].[spCopyCustomer2]
    @CustID AS INT   /* CustID of the customer we want to copy */
AS
BEGIN

--Create a temp table
CREATE TABLE #tSalesOrders2 (id INT IDENTITY (1, 1) NOT NULL, [SOID] INT,[SOID_old] INT );

 --Insert table customer
INSERT INTO tCustomers (CustName)
  SELECT CustName FROM tCustomers
    WHERE tCustomers.CustID = @CustID;

 --Merge to table tSalesOrders for new customer
 Merge tSalesOrders tgt
 using (
Select  SODescription,	SIID,	SIDescription,	so.SOID as SOID_old, CustName, c.CustID, scope_identity() as newCustID
, row_number() Over(partition by so.SOID order by so.SOID) rn
from  tSalesOrders so join tSalesItems si on si.SOID=so.SOID
join tCustomers c on c.CustID=so.CustID
WHERE c.CustID=@CustID
 ) as src on 1=2
When not matched  and src.rn=1 then
Insert (CustID,SODescription) values(src.newCustID,src.SODescription)
OUTPUT inserted.SOID, src.SOID_old
into  #tSalesOrders2 (SOID, SOID_old);

---Insert  to tSalesItems with new SOID
insert into tSalesItems (SOID,SIDescription)
select so2.SOID,si.SIDescription
 from tSalesItems si join #tSalesOrders2 so2 on si.SOID=so2.SOID_old

   --select * from tSalesOrders
   --select * from tSalesItems 

End
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s