none
Inserting Data into two tables one by one

    问题

  • Hi

        I have three tables with below structure. I want to clone the related records of header in the three tables.

    create table QuoteHeader
    (
    HeaderId INT Primary KEY IDENTITY(1,1),
    HeaderDescription VARCHAR(50),
    HeaderPrice INT
    )
    GO
    INSERT INTO QuoteHeader VALUES ('HeadDesc1',45)
    INSERT INTO QuoteHeader VALUES ('HeaderDesc2',60)
    GO
    CREATE TABLE HeaderItems
    (
    HeaderItemID INT Primary Key IDENTITY(1,1),
    HeaderId INT,
    ItemDesc VARCHAR(50),
    ItemCost INT,
    CONSTRAINT fk_HeaderId
      FOREIGN KEY (HeaderId)
      REFERENCES QuoteHeader(HeaderId)
    )
    GO
    INSERT INTO HeaderItems VALUES(1,'ItemDesc1',20)
    INSERT INTO HeaderItems VALUES(1,'ItemDesc1',10)
    INSERT INTO HeaderItems VALUES(1,'ItemDesc1',10)
    INSERT INTO HeaderItems VALUES(2,'ItemDesc2',10)
    INSERT INTO HeaderItems VALUES(2,'ItemDesc2',10)
    GO
    CREATE TABLE HeaderSubscriptionBinding
    (
    HSBID INT PRIMARY KEY IDENTITY(1,1),
    HeaderItemID INT,
    IsActive INT,
    Margin INT,
    CONSTRAINT fk_HeaderItem
      FOREIGN KEY (HeaderItemID)
      REFERENCES HeaderItems(HeaderItemID)
    )
    GO
    INSERT INTO HeaderSubscriptionBinding VALUES(1,1,3)
    INSERT INTO HeaderSubscriptionBinding VALUES(1,1,2)
    INSERT INTO HeaderSubscriptionBinding VALUES(2,1,6)
    INSERT INTO HeaderSubscriptionBinding VALUES(3,1,7)
    INSERT INTO HeaderSubscriptionBinding VALUES(4,1,3)
    INSERT INTO HeaderSubscriptionBinding VALUES(5,1,7)
    INSERT INTO HeaderSubscriptionBinding VALUES(2,1,4)
    INSERT INTO HeaderSubscriptionBinding VALUES(3,1,3)


    I tried the SP like below to insert all the records related to the same records in all three tables.I write an SP with the parameter @HeaderId. When i will give header Id it will insert all the records related to that header ID in the three tables. I dont want to use cursor or while loop like below.

    DECLARE @HeaderId INT
    DECLARE @HeaderIdent INT
    DECLARE @ItemIdent INT
    SET @HeaderId = 1
    
    INSERT INTO dbo.QuoteHeader
    SELECT HeaderDescription,HeaderPrice 
    FROM QuoteHeader WHERE HeaderId = @HeaderId
    
    SET @HeaderIdent = Scope_identity()
    
    DECLARE @t1 TABLE
    (
    RowID INT IDENTITY(1,1),
    HeaderItemID INT
    )
    Insert into @t1
    SELECT HeaderItemID from dbo.HeaderItems 
    WHERE HeaderId = @HeaderId
    
    DECLARE @cnt INT
    SET @cnt = (Select count(0) from @t1)
    
    WHILE @cnt >= 0
    BEGIN
    
    INSERT INTO dbo.HeaderItems
    SELECT @HeaderIdent,ItemDesc,ItemCost 
    FROM  HeaderItems WHERE HeaderItemID = 
    (SELECT HeaderItemID FROM @t1 WHERE RowID = @cnt)
    SET @ItemIdent = Scope_Identity()
    
    INSERT INTO dbo.HeaderSubscriptionBinding
    SELECT @ItemIdent,IsActive,Margin FROM 
    dbo.HeaderSubscriptionBinding WHERE HeaderItemID = 
    (SELECT HeaderItemID FROM @t1 WHERE RowID = @cnt)
    
    
    set @cnt = @cnt -1
    END
    

             Is there any other possibility to write the same query with out using while loop and cursor. Can any one help me out to increase the performance of the above. 

    Thanks in advance

    Dathy



    • 已编辑 dathy 2012年7月5日 21:06
    2012年7月5日 21:02

答案

  • You can use a MERGE command where you make the ON condition always be false (so that the MERGE statement effectively becomes an INSERT statement), then use the OUTPUT clause to save the matches of the value of the identity in the old rows and the value of the identity in the new rows.  Then use those matches to insert rows in the subordinate tables.  Please note this technique only works if you are on SQL 2008 or later.  Using this technique, you can do

    DECLARE @HeaderId INT
    DECLARE @HeaderIdent INT
    DECLARE @ItemIdent INT
    SET @HeaderId = 1
    
    INSERT INTO dbo.QuoteHeader
    SELECT HeaderDescription,HeaderPrice 
    FROM QuoteHeader WHERE HeaderId = @HeaderId
    
    SET @HeaderIdent = Scope_identity()
    
    DECLARE @t1 TABLE
    (
    OldHeaderItemID INT,
    NewHeaderItemID INT
    )
    
    
    Merge Into HeaderItems
    Using (Select HeaderItemID, @HeaderIdent As HeaderIdent, ItemDesc, ItemCost From HeaderItems Where HeaderID = @HeaderId) As s
    On 1 = 0
    When Not Matched Then Insert(HeaderId, ItemDesc, ItemCost) Values (HeaderIdent, ItemDesc, ItemCost)
    Output s.HeaderItemID, inserted.HeaderItemID Into @t1(OldHeaderItemID, NewHeaderItemID);
    
    Insert Into HeaderSubscriptionBinding(HeaderItemID, IsActive, Margin)
    Select t1.NewHeaderItemID, hsb.IsActive, hsb.Margin
    From HeaderSubscriptionBinding hsb
    Inner Join @t1 t1 On hsb.HeaderItemID = t1.OldHeaderItemID;

    Tom

    • 已建议为答案 Naomi NModerator 2012年7月5日 22:36
    • 已标记为答案 dathy 2012年7月6日 11:09
    2012年7月5日 22:19

全部回复

  • You can use a MERGE command where you make the ON condition always be false (so that the MERGE statement effectively becomes an INSERT statement), then use the OUTPUT clause to save the matches of the value of the identity in the old rows and the value of the identity in the new rows.  Then use those matches to insert rows in the subordinate tables.  Please note this technique only works if you are on SQL 2008 or later.  Using this technique, you can do

    DECLARE @HeaderId INT
    DECLARE @HeaderIdent INT
    DECLARE @ItemIdent INT
    SET @HeaderId = 1
    
    INSERT INTO dbo.QuoteHeader
    SELECT HeaderDescription,HeaderPrice 
    FROM QuoteHeader WHERE HeaderId = @HeaderId
    
    SET @HeaderIdent = Scope_identity()
    
    DECLARE @t1 TABLE
    (
    OldHeaderItemID INT,
    NewHeaderItemID INT
    )
    
    
    Merge Into HeaderItems
    Using (Select HeaderItemID, @HeaderIdent As HeaderIdent, ItemDesc, ItemCost From HeaderItems Where HeaderID = @HeaderId) As s
    On 1 = 0
    When Not Matched Then Insert(HeaderId, ItemDesc, ItemCost) Values (HeaderIdent, ItemDesc, ItemCost)
    Output s.HeaderItemID, inserted.HeaderItemID Into @t1(OldHeaderItemID, NewHeaderItemID);
    
    Insert Into HeaderSubscriptionBinding(HeaderItemID, IsActive, Margin)
    Select t1.NewHeaderItemID, hsb.IsActive, hsb.Margin
    From HeaderSubscriptionBinding hsb
    Inner Join @t1 t1 On hsb.HeaderItemID = t1.OldHeaderItemID;

    Tom

    • 已建议为答案 Naomi NModerator 2012年7月5日 22:36
    • 已标记为答案 dathy 2012年7月6日 11:09
    2012年7月5日 22:19
  • It certainly helps if you have SQL 2008 so that you can use the MERGE statement since this helps you to create a mapping between the old and new identity values:

    DECLARE @HeaderId INT
    DECLARE @HeaderIdent INT
    DECLARE @ItemIdent INT
    SET @HeaderId = 1

    INSERT INTO dbo.QuoteHeader
    SELECT HeaderDescription,HeaderPrice
    FROM QuoteHeader WHERE HeaderId = @HeaderId

    SET @HeaderIdent = Scope_identity()

    DECLARE @t1 TABLE  (
       NewHeaderItemID int PRIMARY KEY,
       OldHeaderItemID int UNIQUE
    )

    MERGE dbo.HeaderItems Trg
    USING (SELECT HeaderItemID, ItemDesc, ItemCost
           FROM   dbo.HeaderItems
           WHERE  HeaderId = @HeaderId) AS Src ON 1 = 0
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (HeaderId, ItemDesc, ItemCost)
         VALUES(@HeaderIdent, Src.ItemDesc, Src.ItemCost)
    OUTPUT inserted.HeaderItemID, Src.HeaderItemID INTO @t1
    ;

    INSERT INTO HeaderSubscriptionBinding(HeaderItemID, IsActive, Margin)
       SELECT t.NewHeaderItemID, HSB.IsActive, HSB.Margin
       FROM   HeaderSubscriptionBinding HSB
       JOIN   @t1 t ON HSB.HeaderItemID = t.OldHeaderItemID
    go


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月5日 22:46
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you do not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Rows are not records and IDENTITY cannot be key by definition. That “is_active” looks like flag. WE do not use flags in SQL; that was assembly language What you posted is non-relational garbage. You are trying to fake pointer chains with a count of the physical insertion attempts and nto use a real key. 

    Pick up a book on data modeling and read the parts on keys and on strong versus weak entities. You have gotten it all wrong. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2012年7月6日 5:47
  • Please post DDL,

    Joe Celko, can you please care to read the post you reply to?

    so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are.

    It was all included.

    This is minimal polite behavior on SQL forums. 

    Oh, the pot is calling kettle black again?

    Minimal politeness would say that you would ask for an apology for a completely unhelpful and incorrect post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 9:17