INSERT INTO one table from Multiple Tables

  • I have three tables;

    RT_Group_Average
    RT_Group_Status
    RT_Group_Diff

    I need to Combine the data from both tables and insert it into the third table. The WEA field is the same on both tables 1 and 2, and will be the same in table 3 once they are combined.

    the way the fields map is like this;
    WEA - WEA - WEA in all three
    Date_Time from RT_Group_Status goes into Date_Time Table RT_Group_Diff
    Base1 Table RT_Group_Average goes into PriBase_Ref Table RT_Group_Diff
    Base2 Table RT_Group_Average goes into SecBase_Ref Table RT_Group_Diff
    SSI1 Table RT_Group_Average goes into PriSSI_REF Table RT_Group_Diff
    SSI2 Table RT_Group_Average goes into SecSSI_REF Table RT_Group_Diff
    Base1 Table RT_Group_Status goes into PriBase Table RT_Group_Diff
    Base2 Table RT_Group_Status goes into SecBase Table RT_Group_Diff
    SSI1 Table RT_Group_Status goes into PriSSI Table RT_Group_Diff
    SSI2 Table RT_Group_Status goes into SecSSI Table RT_Group_Diff

    RT_Group_Average is the Static snapshot data;

    USE [nms_rt]
    GO

    /****** Object: Table [dbo].[RT_Group_Average]  Script Date: 11/19/2018 09:47:39 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[RT_Group_Average](
        [ga_id] [int] IDENTITY(1,1) NOT NULL,
        [WEA] [bigint] NULL,
        [Date_Time] [datetime] NULL,
        [Base1] [char](10) NULL,
        [Base2] [char](10) NULL,
        [SSI1] [int] NULL,
        [SSI2] [int] NULL,
        [Average_SSI1] [int] NULL,
        [Average_SSI2] [int] NULL
    ) ON [PRIMARY]
    GO

    RT_Group_Status is Daily occurring data.

    USE [nms_rt]
    GO

    /****** Object: Table [dbo].[RT_Group_Status]  Script Date: 11/19/2018 09:47:52 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[RT_Group_Status](
        [WEA] [dbo].[ATCS_Address] NOT NULL,
        [Date_Time] [datetime] NOT NULL,
        [Base1] [char](10) NULL,
        [Base2] [char](10) NULL,
        [Base3] [char](10) NULL,
        [SSI1] [int] NULL,
        [SSI2] [int] NULL,
        [SSI3] [int] NULL,
        [Lock1] [bit] NULL,
        [Lock2] [bit] NULL,
        [Lock3] [bit] NULL,
        [Pref_base] [char](10) NULL,
        [dbu] [bit] NULL,
        [alarm] [bit] NULL,
    CONSTRAINT [PK_RT_Group_Status] PRIMARY KEY CLUSTERED
    (
        [WEA] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    And RT_Group_Diff is where I need to combine the data from the two.

    USE [nms_rt]
    GO

    /****** Object: Table [dbo].[RT_Group_Diff]  Script Date: 11/19/2018 09:47:46 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[RT_Group_Diff](
        [ga_id] [int] IDENTITY(1,1) NOT NULL,
        [WEA] [bigint] NULL,
        [Date_Time] [datetime] NULL,
        [PriBase_Ref] [char](10) NULL,
        [PriSSI_Ref] [char](10) NULL,
        [PriBase] [char](10) NULL,
        [Pri_SSI] [int] NULL,
        [Pri_Diff] [int] NULL,
        [SecBase_Ref] [char](10) NULL,
        [SecSSI_Ref] [char](10) NULL,
        [SecBase] [char](10) NULL,
        [Sec_SSI] [int] NULL,
        [Sec_Diff] [int] NULL
    ) ON [PRIMARY]
    GO

    What I have so far;

    INSERT INTO RT_Group_Diff(WEA, Date_Time, PriBase_Ref, PriSSI_Ref, PriBase, PriSSI, SecBase_Ref, SecSSI_Ref, SecBase, SecSSI)
    SELECT t1.WEA, t2.Date_Time, t1.Base1, t1.SSI1, t2.Base1, t2.SSI1, t1.Base2, t1.SSI2, t2.Base1, t2.SSI2
    FROM RT_Group_Average t1
    JOIN RT_Group_Status ON  t1.WEA = t2.WEA
    JOIN RT_Group_Diff ON t2.WEA = t3.WEA

    I get Syntax errors. Is this correct?

    Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

  • brian.cook - Monday, November 19, 2018 9:32 AM

    INSERT INTO RT_Group_Diff(WEA, Date_Time, PriBase_Ref, PriSSI_Ref, PriBase, PriSSI, SecBase_Ref, SecSSI_Ref, SecBase, SecSSI)
    SELECT t1.WEA, t2.Date_Time, t1.Base1, t1.SSI1, t2.Base1, t2.SSI1, t1.Base2, t1.SSI2, t2.Base1, t2.SSI2
    FROM RT_Group_Average t1
    JOIN RT_Group_Status ON  t1.WEA = t2.WEA
    JOIN RT_Group_Diff ON t2.WEA = t3.WEA

    I get Syntax errors. Is this correct?

    I don't see any problems with that code. Sharing the error messages would be really useful here.

    On a different note; Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • What's the error you get? Also, remove the insert and write the select. What happens here?

  • Thom A - Monday, November 19, 2018 9:34 AM

    brian.cook - Monday, November 19, 2018 9:32 AM

    INSERT INTO RT_Group_Diff(WEA, Date_Time, PriBase_Ref, PriSSI_Ref, PriBase, PriSSI, SecBase_Ref, SecSSI_Ref, SecBase, SecSSI)
    SELECT t1.WEA, t2.Date_Time, t1.Base1, t1.SSI1, t2.Base1, t2.SSI1, t1.Base2, t1.SSI2, t2.Base1, t2.SSI2
    FROM RT_Group_Average t1
    JOIN RT_Group_Status ON  t1.WEA = t2.WEA
    JOIN RT_Group_Diff ON t2.WEA = t3.WEA

    I get Syntax errors. Is this correct?

    I don't see any problems with that code. Sharing the error messages would be really useful here.

    On a different note; Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3).

    I get this error;
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'RT_Group_Average'.

    Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

  • You don't have an alias for the second or third tables.

    SELECT
       t1.WEA
       , t2.Date_Time
       , t1.Base1
       , t1.SSI1
       , t2.Base1
       , t2.SSI1
       , t1.Base2
       , t1.SSI2
       , t2.Base1
       , t2.SSI2
    FROM
       RT_Group_Average AS t1
      JOIN RT_Group_Status t2
       ON t1.WEA = t2.WEA
      JOIN RT_Group_Diff t3
       ON t2.WEA = t3.WEA
    ;

  • Steve Jones - SSC Editor - Monday, November 19, 2018 9:46 AM

    You don't have an alias for the second or third tables.

    Nice spot Steve! I'd foolishly not spotted that on the basis that the aliases were in the ON. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Monday, November 19, 2018 9:48 AM

    Steve Jones - SSC Editor - Monday, November 19, 2018 9:46 AM

    You don't have an alias for the second or third tables.

    Nice spot Steve! I'd foolishly not spotted that on the basis that the aliases were in the ON. 🙂

    Okay, query executes now, but I get 0 rows affected.

    Both the RT_Group_Average and RT_Group_Status have data. 0 rows makes no sense.

    Duh! on my part about the aliases. Thanks for that catch.

    Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

  • brian.cook - Monday, November 19, 2018 10:03 AM

    Thom A - Monday, November 19, 2018 9:48 AM

    Nice spot Steve! I'd foolishly not spotted that on the basis that the aliases were in the ON. 🙂

    Okay, query executes now, but I get 0 rows affected.

    Both the RT_Group_Average and RT_Group_Status have data. 0 rows makes no sense.

    Duh! on my part about the aliases. Thanks for that catch.

    Is it because you're always joining to RT_Group_Diff, which is the table your inserting into as well (it makes little sense to join to the table and insert rows that already exist).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • brian.cook - Monday, November 19, 2018 10:03 AM

    Thom A - Monday, November 19, 2018 9:48 AM

    Steve Jones - SSC Editor - Monday, November 19, 2018 9:46 AM

    You don't have an alias for the second or third tables.

    Nice spot Steve! I'd foolishly not spotted that on the basis that the aliases were in the ON. 🙂

    Okay, query executes now, but I get 0 rows affected.

    Both the RT_Group_Average and RT_Group_Status have data. 0 rows makes no sense.

    Duh! on my part about the aliases. Thanks for that catch.

    If you do an INNER JOIN on all tables then all tables need to have rows, they also need to have rows that match the joins.

  • Thom A - Monday, November 19, 2018 10:05 AM

    brian.cook - Monday, November 19, 2018 10:03 AM

    Thom A - Monday, November 19, 2018 9:48 AM

    Steve Jones - SSC Editor - Monday, November 19, 2018 9:46 AM

    You don't have an alias for the second or third tables.

    Nice spot Steve! I'd foolishly not spotted that on the basis that the aliases were in the ON. 🙂

    Okay, query executes now, but I get 0 rows affected.

    Both the RT_Group_Average and RT_Group_Status have data. 0 rows makes no sense.

    Duh! on my part about the aliases. Thanks for that catch.

    Is it because you're always joining to RT_Group_Diff, which is the table your inserting into as well (it makes little sense to join to the table and insert rows that already exist).

    Ah Ha! Good Point. Dropping the Join to the RT_Group_Diff did the trick. Thanks much!!!

    Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Viewing 10 posts - 1 through 9 (of 9 total)

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