Insert into one table from multiple tables

  • Hi All,

    Just needed a bit of help with an insert query.

    I have 3 tables that I need data from each to populate a 4th master.

    Table1 = Risk Data, this table has each risk assesment for each client and can contain multiple risk assesments. I need to return the latest risk assesment and populate a portion of the master table with it's latest risk score and risk level.

    Table2 = Entity Data, this table contains the master entity and I need to return a couple of fields: Entity No and Entity Type from here into the master table.

    Table 3 = Financial Data, this table holds multiple financial records and year end data. Each entity can have multiple entries in here and I need to grab 2 dates. Latest Financial year End and the last Financial year end.

    I can work out how to do a single table insert but I just can't get my head around doing this from multiple tables!?!?!?!? I can work out the use of MAX to return the last dates but not sure how to return the penultimate date, i.e. the one before last.

    Any help is very much appreciated.

  • Sorry did I miss the bit that said SQL for newbies????

    Really, you shouldn't have bothered. I asked for help and all I got from you was insulting at best... please go away and annoy someone else with your pathetic analogy of 20th century IT as I am more than sure that someone with a brain might actually be of assitance.

    I am not a programmer and I don't want to be a programmer, especially if I end up a pompous d*ck like you. That I belive is a STATEMENT?!

    You know exactly what I mean otherwise you wouldn't have suggested a "vague generalisation" of a view. My issue is how to query (apologises I thought SQL stood for structured query language?!) 3 tables and populate 1 with information from the 3. Not a difficult concept to grasp from someone of your much higher intelligence (sic)?

    All I want is a brief code snippet on how to code 3 bits of data into 1 table. There are reasons behind the fact that this isn't held in a temp table or that I don't want it in a view with which my initial ask of assistance didn't merit boring people with and actually still doesn't.

    So are you going to help or just spout off?

  • daft,

    We'll need more info on the schema. What field(s) link the records in all tables?

    Mark

  • daftmoo (8/18/2012)


    Hi All,

    Just needed a bit of help with an insert query.

    I have 3 tables that I need data from each to populate a 4th master.

    Table1 = Risk Data, this table has each risk assesment for each client and can contain multiple risk assesments. I need to return the latest risk assesment and populate a portion of the master table with it's latest risk score and risk level.

    Table2 = Entity Data, this table contains the master entity and I need to return a couple of fields: Entity No and Entity Type from here into the master table.

    Table 3 = Financial Data, this table holds multiple financial records and year end data. Each entity can have multiple entries in here and I need to grab 2 dates. Latest Financial year End and the last Financial year end.

    I can work out how to do a single table insert but I just can't get my head around doing this from multiple tables!?!?!?!? I can work out the use of MAX to return the last dates but not sure how to return the penultimate date, i.e. the one before last.

    Any help is very much appreciated.

    To help you please help us to get table DDL and sample records along with the expected output.

    Please have a look on the url in my signature.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • daftmoo (8/18/2012)


    Sorry did I miss the bit that said SQL for newbies????

    .......................................

    So are you going to help or just spout off?

    hmmmm...not really the best response for someone who has just joined the forum and is asking for help :ermm:

    moving on.....have knocked together some code below.

    does the table structure and sample data provide enough details to explain your problem?

    I have also bolted on some ctes that do what I think you are asking for....but who really knows...other than you, at this stage.

    there are other ways...probably more efficient...which I am sure people will offer, if they believe they understand your problems correctly.

    treat this code as work in progress to help you, help yourself.

    good luck...

    --=== set up some data that demonstrates the problem

    USE [tempdb]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RiskData]') AND type in (N'U'))

    DROP TABLE [dbo].[RiskData]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityType]') AND type in (N'U'))

    DROP TABLE [dbo].[EntityType]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinData]') AND type in (N'U'))

    DROP TABLE [dbo].[FinData]

    GO

    CREATE TABLE [dbo].[RiskData](

    [RiskId] [int] NULL,

    [ClientId] [int] NULL,

    [RiskScore] [int] NULL,

    [RiskLevel] [int] NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[EntityType](

    [ClientId] [int] NULL,

    [EntityNo] [int] NULL,

    [EntityType] [int] NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[FinData](

    [ClientID] [int] NULL,

    [FinYear] [int] NULL,

    [FinData] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO [dbo].[RiskData]([RiskId], [ClientId], [RiskScore], [RiskLevel])

    SELECT 1, 1, 10, 3 UNION ALL

    SELECT 2, 2, 5, 4 UNION ALL

    SELECT 3, 3, 12, 5 UNION ALL

    SELECT 4, 1, 8, 2 UNION ALL

    SELECT 5, 2, 15, 8 UNION ALL

    SELECT 6, 3, 4, 3

    INSERT INTO [dbo].[EntityType]([ClientId], [EntityNo], [EntityType])

    SELECT 1, 2, 3 UNION ALL

    SELECT 2, 2, 4 UNION ALL

    SELECT 3, 4, 1

    INSERT INTO [dbo].[FinData]([ClientID], [FinYear], [FinData])

    SELECT 1, 2012, 123456 UNION ALL

    SELECT 2, 2012, 654321 UNION ALL

    SELECT 3, 2011, 369852 UNION ALL

    SELECT 1, 2011, 125487 UNION ALL

    SELECT 2, 2011, 582369 UNION ALL

    SELECT 3, 2010, 412587 UNION ALL

    SELECT 1, 2010, 654233 UNION ALL

    SELECT 2, 2010, 556644 UNION ALL

    SELECT 3, 2009, 321456

    ;

    --==== do some divide and conquer to get the subsets of data required

    with cte_RD as

    (

    SELECT ClientId, MAX(RiskId)MaxID

    FROM RiskData

    GROUP BY ClientId

    )

    ,

    cte_RD2 as

    (

    SELECT rd.ClientId ,

    rd.RiskScore ,

    rd.RiskLevel

    FROM

    RiskData AS rd INNER JOIN cte_RD ON rd.ClientId = cte_RD.ClientId

    AND rd.RiskId = cte_RD.MaxId

    )

    ,

    cte_FD as

    (

    SELECT ClientID ,

    FinYear ,

    FinData ,

    ROW_NUMBER( )OVER( PARTITION BY clientid ORDER BY finyear DESC )AS rn

    FROM findata

    )

    ,

    cte_fd2 as

    (

    SELECT ClientID ,

    MAX(CASE WHEN rn = 1 THEN finyear ELSE NULL END) AS lastFinyear,

    MAX(CASE WHEN rn = 1 THEN findata ELSE NULL END) AS lastFindata,

    MAX(CASE WHEN rn = 2 THEN finyear ELSE NULL END) AS pen_lastFinyear,

    MAX(CASE WHEN rn = 2 THEN findata ELSE NULL END) AS pen_lastFindata

    FROM cte_FD

    WHERE (rn < 3)

    GROUP BY ClientID

    )

    --=== stitch it back together

    SELECT ET.ClientId ,

    ET.EntityNo ,

    ET.EntityType ,

    cte_fd2.lastFinyear ,

    cte_fd2.lastFindata ,

    cte_fd2.pen_lastFinyear ,

    cte_fd2.pen_lastFindata ,

    cte_rd2.RiskScore ,

    cte_rd2.RiskLevel

    FROM

    EntityType AS ET INNER JOIN cte_fd2 ON ET.ClientId = cte_fd2.ClientID

    INNER JOIN cte_rd2 ON ET.ClientId = cte_rd2.ClientId;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thank you Livingston that is exactly spot on! All I wanted really was an example of how to get data from 3 tables and populate 1 with the information, now you have shown me that, it all makes sense. I didn't realise but your analagy of divide and conquer made immediate sense and I should have taken all of this and started with each individual bit and then brought them all together.

    Thank you very, very much!

    Apologies if I came across as being a bit abrupt, but I took offence (after a very long day) to the response I got. If the intention was to educate then fine I will take the response as that intention and apologise for my rude behaviour.

    As I said I am not a programmer merely someone who has got lumbered with a job to do and no else who can be bothered to do it! I have no idea what DDL is? So apologies but I thought newbie meant just that?! This is (apart from a few SELECT statements) my first foray into SQL. I have no experience, I am not a programmer and have never been taught any of this and what I have gleaned so far is all from the internet... I doubt in the future I will ever venture down this path again.

    Thanks once again Livingston! Much appreciated.

    J Livingston SQL (8/19/2012)


    daftmoo (8/18/2012)


    Sorry did I miss the bit that said SQL for newbies????

    .......................................

    So are you going to help or just spout off?

    hmmmm...not really the best response for someone who has just joined the forum and is asking for help :ermm:

    moving on.....have knocked together some code below.

    does the table structure and sample data provide enough details to explain your problem?

    I have also bolted on some ctes that do what I think you are asking for....but who really knows...other than you, at this stage.

    there are other ways...probably more efficient...which I am sure people will offer, if they believe they understand your problems correctly.

    treat this code as work in progress to help you, help yourself.

    good luck...

    --=== set up some data that demonstrates the problem

    USE [tempdb]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RiskData]') AND type in (N'U'))

    DROP TABLE [dbo].[RiskData]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityType]') AND type in (N'U'))

    DROP TABLE [dbo].[EntityType]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinData]') AND type in (N'U'))

    DROP TABLE [dbo].[FinData]

    GO

    CREATE TABLE [dbo].[RiskData](

    [RiskId] [int] NULL,

    [ClientId] [int] NULL,

    [RiskScore] [int] NULL,

    [RiskLevel] [int] NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[EntityType](

    [ClientId] [int] NULL,

    [EntityNo] [int] NULL,

    [EntityType] [int] NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[FinData](

    [ClientID] [int] NULL,

    [FinYear] [int] NULL,

    [FinData] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO [dbo].[RiskData]([RiskId], [ClientId], [RiskScore], [RiskLevel])

    SELECT 1, 1, 10, 3 UNION ALL

    SELECT 2, 2, 5, 4 UNION ALL

    SELECT 3, 3, 12, 5 UNION ALL

    SELECT 4, 1, 8, 2 UNION ALL

    SELECT 5, 2, 15, 8 UNION ALL

    SELECT 6, 3, 4, 3

    INSERT INTO [dbo].[EntityType]([ClientId], [EntityNo], [EntityType])

    SELECT 1, 2, 3 UNION ALL

    SELECT 2, 2, 4 UNION ALL

    SELECT 3, 4, 1

    INSERT INTO [dbo].[FinData]([ClientID], [FinYear], [FinData])

    SELECT 1, 2012, 123456 UNION ALL

    SELECT 2, 2012, 654321 UNION ALL

    SELECT 3, 2011, 369852 UNION ALL

    SELECT 1, 2011, 125487 UNION ALL

    SELECT 2, 2011, 582369 UNION ALL

    SELECT 3, 2010, 412587 UNION ALL

    SELECT 1, 2010, 654233 UNION ALL

    SELECT 2, 2010, 556644 UNION ALL

    SELECT 3, 2009, 321456

    ;

    --==== do some divide and conquer to get the subsets of data required

    with cte_RD as

    (

    SELECT ClientId, MAX(RiskId)MaxID

    FROM RiskData

    GROUP BY ClientId

    )

    ,

    cte_RD2 as

    (

    SELECT rd.ClientId ,

    rd.RiskScore ,

    rd.RiskLevel

    FROM

    RiskData AS rd INNER JOIN cte_RD ON rd.ClientId = cte_RD.ClientId

    AND rd.RiskId = cte_RD.MaxId

    )

    ,

    cte_FD as

    (

    SELECT ClientID ,

    FinYear ,

    FinData ,

    ROW_NUMBER( )OVER( PARTITION BY clientid ORDER BY finyear DESC )AS rn

    FROM findata

    )

    ,

    cte_fd2 as

    (

    SELECT ClientID ,

    MAX(CASE WHEN rn = 1 THEN finyear ELSE NULL END) AS lastFinyear,

    MAX(CASE WHEN rn = 1 THEN findata ELSE NULL END) AS lastFindata,

    MAX(CASE WHEN rn = 2 THEN finyear ELSE NULL END) AS pen_lastFinyear,

    MAX(CASE WHEN rn = 2 THEN findata ELSE NULL END) AS pen_lastFindata

    FROM cte_FD

    WHERE (rn < 3)

    GROUP BY ClientID

    )

    --=== stitch it back together

    SELECT ET.ClientId ,

    ET.EntityNo ,

    ET.EntityType ,

    cte_fd2.lastFinyear ,

    cte_fd2.lastFindata ,

    cte_fd2.pen_lastFinyear ,

    cte_fd2.pen_lastFindata ,

    cte_rd2.RiskScore ,

    cte_rd2.RiskLevel

    FROM

    EntityType AS ET INNER JOIN cte_fd2 ON ET.ClientId = cte_fd2.ClientID

    INNER JOIN cte_rd2 ON ET.ClientId = cte_rd2.ClientId;

  • keith.mcelroy (8/20/2012)


    Shut up

    ???

    sorry...but dont understand your comment.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply