locked
Insertion into multiple tables (including bridge entity) RRS feed

  • Question

  • I've studied rationale Database development but haven't used it practically.

    I am trying to develop a database to gather financial data (KPIs) from multiple companies


    Since most of the KPIs are fixed (but some companies has more or less KPIs), I decided to use a bridge entity between the company table and KPI data. The bridge entity includes the values and the date.

    But I can't figure out how to insert the data. keep in mind that I don't want to use the ID of the Companies and KPIs because of the large number of data. I want to use the "Names" of KPI and Company. and When a KPI doesn't exist, it should be inserted into the KPI table

    I will include a part of my ERD in the attachment.

    P.S. I have no problem using ASP.NET (If there is no way to solve it through SQL server only)


    • Edited by iHussain Saturday, July 9, 2011 10:10 AM Add Screen shot
    Saturday, July 9, 2011 10:00 AM

Answers

  • Is it kpi_id UNIQUE? Can you show us sample data you inserted into

    DECLARE @kpi_name VARCHAR(20)

    SET @kpi_name ='whatever'

    IF NOT EXISTS (SELECT * FROM tb_kpi WHERE kpi_name=@kpi_name)

    BEGIN

    INSERT INTO tb_kpi(kpi_name) VALUES (@kpi_name)

    END 

    Note, that makes you create a unique constraint on the kpi_name column name...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 10, 2011 5:50 AM
  • I'd create a stored proc called something like "prc_InsertKPIValue" as follows:

    CREATE PROC prc_InsertKPIValue
      @c_Name nvarchar(200),
      @kpi_Name nvarchar(200),
      @Value numeric(18,0),
      @Date datetime
    AS
    DECLARE @c_ID int;
    DECLARE @kpi_ID int;
    
    SELECT @c_ID = c_ID
    FROM tb_Company
    WHERE c_Name = @c_Name;
    
    SELECT @kpi_ID = kpi_ID
    FROM tb_KPI
    WHERE kpi_Name = @kpi_Name;
    
    INSERT INTO tb_Values
    (
      c_ID,
      kpi_ID,
      Value,
      Date
    )
    VALUES 
    (
      @c_ID, 
      @kpi_ID,
      @Value,
      @Date
    );
    GO;
    
    
    

    If you want to upload a batch of KPI values all at once, you can create a table type that matches the signiature of the parameter list above and use a table-valued parameter based on that table type as your input.  In that case you'll use a SELECT query against your parameter table joined against the tb_Company and tb_KPI tables to give you the c_ID and kpi_ID fields.

    For the record, you can't drop a table type while there are objects still referencing it - SQL Server won't necessarily give you an error message saying that it couldn't drop the table type but you'll get complaints when you compile a stored proc that expects new input table parameter fields that don't exist in the type.

    HTH

     


    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    Tuesday, July 12, 2011 5:45 AM

All replies

  • If I understand correctly...

    Your schema definition looks al-right for your requirement... If company has KPI then record will be inserted in all three tables ie company name in tbl_company and KPIs in tbl_KPI then pick the IDs from both table and insert information in tbl_values....

    Company does not defined any KPIs only company details will be inserts in tbl_Company.... You can use Left Join to tie these tables...

     


    http://uk.linkedin.com/in/ramjaddu
    Saturday, July 9, 2011 7:20 PM
  • Is it kpi_id UNIQUE? Can you show us sample data you inserted into

    DECLARE @kpi_name VARCHAR(20)

    SET @kpi_name ='whatever'

    IF NOT EXISTS (SELECT * FROM tb_kpi WHERE kpi_name=@kpi_name)

    BEGIN

    INSERT INTO tb_kpi(kpi_name) VALUES (@kpi_name)

    END 

    Note, that makes you create a unique constraint on the kpi_name column name...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 10, 2011 5:50 AM
  • I'd create a stored proc called something like "prc_InsertKPIValue" as follows:

    CREATE PROC prc_InsertKPIValue
      @c_Name nvarchar(200),
      @kpi_Name nvarchar(200),
      @Value numeric(18,0),
      @Date datetime
    AS
    DECLARE @c_ID int;
    DECLARE @kpi_ID int;
    
    SELECT @c_ID = c_ID
    FROM tb_Company
    WHERE c_Name = @c_Name;
    
    SELECT @kpi_ID = kpi_ID
    FROM tb_KPI
    WHERE kpi_Name = @kpi_Name;
    
    INSERT INTO tb_Values
    (
      c_ID,
      kpi_ID,
      Value,
      Date
    )
    VALUES 
    (
      @c_ID, 
      @kpi_ID,
      @Value,
      @Date
    );
    GO;
    
    
    

    If you want to upload a batch of KPI values all at once, you can create a table type that matches the signiature of the parameter list above and use a table-valued parameter based on that table type as your input.  In that case you'll use a SELECT query against your parameter table joined against the tb_Company and tb_KPI tables to give you the c_ID and kpi_ID fields.

    For the record, you can't drop a table type while there are objects still referencing it - SQL Server won't necessarily give you an error message saying that it couldn't drop the table type but you'll get complaints when you compile a stored proc that expects new input table parameter fields that don't exist in the type.

    HTH

     


    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    Tuesday, July 12, 2011 5:45 AM