locked
insert data to linked table RRS feed

  • Question

  • haven these table in sql database :

            table 1 has columns ( id1 , C1)

               table 2 has columns ( id2, C2)

              table 3 ( id1 , id2)

    id 1 and id2 is asthmatically generated each time i insert data to these table , I want at the same time that i add new row to table 1 and table 2 , to inserting data to table 3

     

    Thursday, October 13, 2011 10:12 AM

Answers

  • Hi,

    You have to use Triggers.

    See the below example for INSERT TRIGGERS

    CREATE TRIGGER trgAfterInsert ON [dbo].[ table1 ] 
    FOR INSERT
    AS
    	declare @id1 int;
    	declare @C1 varchar(100);
    	
    	select @id1 =i. id1 from inserted i;	
    	select @C1=i. C1 from inserted i;	
    	
    
    	insert into  table3 (id1, id2) values(@id1,@C1);
    
    	
    GO
    Note :The above Trigger is fired when you insert Records for Table1 only.

    PS.Shakeer Hussain
    • Proposed as answer by ForumFAQ Monday, October 17, 2011 2:22 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:50 AM
    Thursday, October 13, 2011 11:02 AM
  • Hi,
    If you are doing it inside stored procedure you can use this method.

    CREATE TABLE [dbo].[Test1](
    	[ID1] [int] IDENTITY(1,1) NOT NULL,
    	[C1] [nchar](10) NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[Test2](
    	[ID2] [int] IDENTITY(1,1) NOT NULL,
    	[C2] [nchar](10) NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[Test3]
    (
    	[ID1] [int],
    	[ID2] INT
    ) ON [PRIMARY]
    
    GO
    
    DECLARE @ID1 INT
    DECLARE @ID2 INT
    
    INSERT INTO [Test1]
    SELECT 'A'
    
    SELECT @ID1 = @@IDENTITY 
    
    INSERT INTO [Test2]
    SELECT 'B'
    
    SELECT @ID2 = @@IDENTITY 
    
    INSERT INTO [Test3]
    SELECT @ID1,@ID2
    
    DROP TABLE [Test1]
    DROP TABLE [Test2]
    DROP TABLE [Test3]
    

    Shatrughna.
    • Proposed as answer by Iric Wen Monday, October 24, 2011 1:07 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:50 AM
    Thursday, October 20, 2011 12:06 PM

All replies

  • Hi,

    You have to use Triggers.

    See the below example for INSERT TRIGGERS

    CREATE TRIGGER trgAfterInsert ON [dbo].[ table1 ] 
    FOR INSERT
    AS
    	declare @id1 int;
    	declare @C1 varchar(100);
    	
    	select @id1 =i. id1 from inserted i;	
    	select @C1=i. C1 from inserted i;	
    	
    
    	insert into  table3 (id1, id2) values(@id1,@C1);
    
    	
    GO
    Note :The above Trigger is fired when you insert Records for Table1 only.

    PS.Shakeer Hussain
    • Proposed as answer by ForumFAQ Monday, October 17, 2011 2:22 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:50 AM
    Thursday, October 13, 2011 11:02 AM
  • As Syed mentioned above the trigger is an option. Alternatively you can control this in your stored procedure that is inserting into Table 1 & Table 2. This takes away the possible overhead of the triggers but means your stored proc code needs to perform the 2 inserts first and retrieve the data just inserted and perform your 3rd insert. Having FK's on both of the ID's will help ensure your integrity.

    Just another option.

    I hope this helps


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Friday, October 14, 2011 2:50 PM
  • Hi,
    If you are doing it inside stored procedure you can use this method.

    CREATE TABLE [dbo].[Test1](
    	[ID1] [int] IDENTITY(1,1) NOT NULL,
    	[C1] [nchar](10) NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[Test2](
    	[ID2] [int] IDENTITY(1,1) NOT NULL,
    	[C2] [nchar](10) NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[Test3]
    (
    	[ID1] [int],
    	[ID2] INT
    ) ON [PRIMARY]
    
    GO
    
    DECLARE @ID1 INT
    DECLARE @ID2 INT
    
    INSERT INTO [Test1]
    SELECT 'A'
    
    SELECT @ID1 = @@IDENTITY 
    
    INSERT INTO [Test2]
    SELECT 'B'
    
    SELECT @ID2 = @@IDENTITY 
    
    INSERT INTO [Test3]
    SELECT @ID1,@ID2
    
    DROP TABLE [Test1]
    DROP TABLE [Test2]
    DROP TABLE [Test3]
    

    Shatrughna.
    • Proposed as answer by Iric Wen Monday, October 24, 2011 1:07 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:50 AM
    Thursday, October 20, 2011 12:06 PM