November 19, 2018 at 9:32 am
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
November 19, 2018 at 9:34 am
brian.cook - Monday, November 19, 2018 9:32 AMINSERT 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.WEAI 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
November 19, 2018 at 9:35 am
What's the error you get? Also, remove the insert and write the select. What happens here?
November 19, 2018 at 9:37 am
Thom A - Monday, November 19, 2018 9:34 AMbrian.cook - Monday, November 19, 2018 9:32 AMINSERT 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.WEAI 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
November 19, 2018 at 9:46 am
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
;
November 19, 2018 at 9:48 am
Steve Jones - SSC Editor - Monday, November 19, 2018 9:46 AMYou 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
November 19, 2018 at 10:03 am
Thom A - Monday, November 19, 2018 9:48 AMSteve Jones - SSC Editor - Monday, November 19, 2018 9:46 AMYou 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
November 19, 2018 at 10:05 am
brian.cook - Monday, November 19, 2018 10:03 AMThom A - Monday, November 19, 2018 9:48 AMNice 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
November 19, 2018 at 10:07 am
brian.cook - Monday, November 19, 2018 10:03 AMThom A - Monday, November 19, 2018 9:48 AMSteve Jones - SSC Editor - Monday, November 19, 2018 9:46 AMYou 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.
November 19, 2018 at 10:07 am
Thom A - Monday, November 19, 2018 10:05 AMbrian.cook - Monday, November 19, 2018 10:03 AMThom A - Monday, November 19, 2018 9:48 AMSteve Jones - SSC Editor - Monday, November 19, 2018 9:46 AMYou 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