locked
Insert Data from table to another automatically RRS feed

  • Question

  • Hello,

    I'm creating and vb.net app which based on MS SQL Server DB and in my DB i have 2 different tables Storage and POS

    Storage and POS have 2 Columns Code and Item which i need

    what i need is when i add new row in Storage automatically a new row add to POS and the columns (Code and Item) Value add from Storage to POS and also make these columns up to date.

    i tried the computed columns but i done some research and found that is not possible to insert and read data from another table by computed columns function.

    So any ideas?


    • Edited by AbanoubZak Sunday, May 19, 2019 3:03 PM
    Sunday, May 19, 2019 3:01 PM

Answers

  • Please try with below code:


    CREATE TABLE dbo.Storage (ID INT, Item VARCHAR(50))
    CREATE TABLE dbo.Pos (ID INT, Item VARCHAR(50))

    INSERT INTO dbo.Storage
    OUTPUT 
        INSERTED.ID
        ,INSERTED.Item    
      INTO dbo.Pos
    VALUES(1, 'A');

    SELECT * FROM dbo.Storage
    SELECT * FROM dbo.Pos

    DECLARE @Test TABLE (ID INT, Item VARCHAR(50))

    UPDATE dbo.Storage
    SET Item = 'B'
    OUTPUT
    INSERTED.ID
    ,INSERTED.Item    
      INTO @Test 
    WHERE Id = 1

    select * from @Test

    UPDATE p
    SET p.Item = t.Item
    FROM dbo.Pos p
    INNER JOIN @Test t
    ON p.Id = t.Id

    SELECT * from dbo.Pos

    Monday, May 20, 2019 10:25 AM

All replies

  • I'm not sure I understand the question correctly, but this trigger should copy all new rows in table Storage to the table POS.

    CREATE TRIGGER storage_insert_tri 
       ON  Storage 
       AFTER INSERT
    AS 
    BEGIN
       INSERT POS (
                Code
               ,Item  )
       SELECT   Code
               ,Item
       FROM     inserted 
    END

    Monday, May 20, 2019 8:22 AM
  • I'm not sure I understand the question correctly, but this trigger should copy all new rows in table Storage to the table POS.

    CREATE TRIGGER storage_insert_tri 
       ON  Storage 
       AFTER INSERT
    AS 
    BEGIN
       INSERT POS (
                Code
               ,Item  )
       SELECT   Code
               ,Item
       FROM     inserted 
    END

    Thank you for your Reply.

    I understand from the code that when I insert a data in Storage it will be insert to  POS also that's good but how to make it also update so when i change the data in Storage the changes will be made in POS too?

    Let me put it in that phrase(i need to used it as if it was Excel linked Cells so when i change data or add data it will change or added also to the other linked cells)


    • Edited by AbanoubZak Monday, May 20, 2019 8:34 AM
    Monday, May 20, 2019 8:31 AM
  • Please use below two options to resolve your issue:

    1. Output Clause

    2. Triggers

    Monday, May 20, 2019 8:35 AM
  • Please use below two options to resolve your issue:

    1. Output Clause

    2. Triggers

    Thank you for your Reply.

    I'm kinda new to MS SQL Server but can you show me an example to the Output Query how it will be

    and also i'm not expert Of course but this will need me to make an execute every time but i need this to be done automatically in the background with out any  interference from me or the app user, can it be done?
    • Edited by AbanoubZak Monday, May 20, 2019 9:00 AM
    Monday, May 20, 2019 8:56 AM
  • Please try with below code:


    CREATE TABLE dbo.Storage (ID INT, Item VARCHAR(50))
    CREATE TABLE dbo.Pos (ID INT, Item VARCHAR(50))

    INSERT INTO dbo.Storage
    OUTPUT 
        INSERTED.ID
        ,INSERTED.Item    
      INTO dbo.Pos
    VALUES(1, 'A');

    SELECT * FROM dbo.Storage
    SELECT * FROM dbo.Pos

    DECLARE @Test TABLE (ID INT, Item VARCHAR(50))

    UPDATE dbo.Storage
    SET Item = 'B'
    OUTPUT
    INSERTED.ID
    ,INSERTED.Item    
      INTO @Test 
    WHERE Id = 1

    select * from @Test

    UPDATE p
    SET p.Item = t.Item
    FROM dbo.Pos p
    INNER JOIN @Test t
    ON p.Id = t.Id

    SELECT * from dbo.Pos

    Monday, May 20, 2019 10:25 AM