SELECT INTO doesn't update the tables recursively

• rva.raghav

  SSC Enthusiast

  Points: 151

  More actions

  March 21, 2018 at 9:35 pm

  #327995

  I have the below query which merges 9 tables into a single table NBCVadodra but doesn't get updated everytime if there are changes in the source table. How do I fix this ?

  SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till INTO NBCVadodra
FROM
(SELECT Tstamp, Inverter_id, AC_Power, total_energy_generated_till FROM [dbo].[IN-023C-I1]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I2]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I3]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I4]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I5]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I6]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I7]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I8]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I9]
  )t

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 21, 2018 at 9:48 pm

  #1984217

  rva.raghav - Wednesday, March 21, 2018 9:35 PM

  I have the below query which merges 9 tables into a single table NBCVadodra but doesn't get updated everytime if there are changes in the source table. How do I fix this ?

  SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till INTO NBCVadodra
FROM
(SELECT Tstamp, Inverter_id, AC_Power, total_energy_generated_till FROM [dbo].[IN-023C-I1]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I2]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I3]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I4]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I5]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I6]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I7]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I8]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I9]
  )t

  First of all, you do use SELECT INTO unless you are creating the table from scratch each time.
  f you are inserting new data and updating existing data you should be using the MERGE statement.
  I

• rva.raghav

  SSC Enthusiast

  Points: 151

  More actions

  March 21, 2018 at 10:10 pm

  #1984218

  Merge statement doesn't combine tables. Stack them in rows one by one. Can you suggest some other way ?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 21, 2018 at 10:58 pm

  #1984221

  rva.raghav - Wednesday, March 21, 2018 10:10 PM

  Merge statement doesn't combine tables. Stack them in rows one by one. Can you suggest some other way ?

  Haven't used MERGE a lot but I would beg to differ with you.  In your original code you combined all the source tables into a erived table.  You can do the same thing with MERGE.  You could also change the derived table into a CTE and use that as your source table.

• subramaniam.chandrasekar

  SSCarpal Tunnel

  Points: 4223

  More actions

  March 21, 2018 at 11:28 pm

  #1984222

  rva.raghav - Wednesday, March 21, 2018 10:10 PM

  Merge statement doesn't combine tables. Stack them in rows one by one. Can you suggest some other way ?

  Yes, You could try use CTE instead...

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 22, 2018 at 9:38 am

  #1984301

  I'm pretty sure what you want is a view:
  CREATE VIEW NBCVadodra
AS
SELECT Tstamp, Inverter_id, AC_Power, total_energy_generated_till
FROM [dbo].[IN-023C-I1]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I2]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I3]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I4]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I5]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I6]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I7]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I8]
UNION ALL
SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till
FROM [dbo].[IN-023C-I9]
GO

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1003888

  More actions

  March 22, 2018 at 3:38 pm

  #1984362

  rva.raghav - Wednesday, March 21, 2018 9:35 PM

  I have the below query which merges 9 tables into a single table NBCVadodra but doesn't get updated everytime if there are changes in the source table. How do I fix this ?

  SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till INTO NBCVadodra
FROM
(SELECT Tstamp, Inverter_id, AC_Power, total_energy_generated_till FROM [dbo].[IN-023C-I1]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I2]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I3]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I4]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I5]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I6]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I7]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I8]UNION ALL SELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I9]
  )t

  Convert your query to a partitioned view and everything will update magically.  If you do it right, it'll be an updateable view, to boot.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• rva.raghav

  SSC Enthusiast

  Points: 151

  More actions

  March 22, 2018 at 8:11 pm

  #1984373

  Hi sgmunson,

  Thanks for the answer. Appreciate it. I would like to know how to write a sub view inside this view.

  For example a calculation like this.

  CREATE VIEW MeterReadings
  AS
  SELECT Tstamp,Inverter_ID
    ,MAX(total_energy_generated_till)-MIN(total_energy_generated_till) [total_energy_generated_till] FROM NBCVadodra
  GROUP BY Tstamp, Inverter_ID

• Jeff Moden

  SSC Guru

  Points: 1003888

  More actions

  March 22, 2018 at 9:45 pm

  #1984375

  rva.raghav - Thursday, March 22, 2018 8:11 PM

  Hi sgmunson,

  Thanks for the answer. Appreciate it. I would like to know how to write a sub view inside this view.

  For example a calculation like this.

  CREATE VIEW MeterReadings
  AS
  SELECT Tstamp,Inverter_ID
    ,MAX(total_energy_generated_till)-MIN(total_energy_generated_till) [total_energy_generated_till] FROM NBCVadodra
  GROUP BY Tstamp, Inverter_ID

  That would likely be a mistake.  Treat the view that SGMunson wrote as if it were a table.  Better yet, look up "Partitioned Views" and learn how to create them so that the underlying tables aren't only readable through the view but they'll be updateable through the view, as well.

  The reason why you don't want a GROUP BY/Aggregates in the view is because someone will certainly try to apply criteria to the aggregates in the view and the view will have to materialize all the tables to come up with the answer instead of just (possibly) reading from one table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)