Post reply

SELECT INTO doesn't update the tables recursively

  • March 21, 2018 at 9:33 pm

    #327994

    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 ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I2]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I3]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I4]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo]. [IN-023C-I5]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I6]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I7]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I8]UNION ALLSELECT Tstamp, Inverter_id, AC_Power,total_energy_generated_till FROM [dbo].[IN-023C-I9]))t

  • March 22, 2018 at 12:12 am

    #1984225

    If you take a photograph of yourself, and then dye your hair, does the hair colour in the photograph change?
    You've effectively taken a snapshot of the data in the 9 tables and placed that in a new table.

    If you need the 1 table to always represent the 9 tables, then you have 2 options

    • Create a view which spans the 9 tables.  Depending on the size of the tables, you may need a schema-bound view with indexes.
    • Create insert, update, delete triggers on the 9 tables which update the new table whenever the data changes.

    How to post data/code on a forum to get the best help.

  • March 22, 2018 at 3:31 am

    #1984231

    Please don't post the same question in multiple places.
    No further replies here please. Replies to:  https://www.sqlservercentral.com/Forums/1928776/SELECT-INTO-doesnt-update-the-tables-recursively

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2018 at 10:56 am

    #1984332

    Closing

Viewing 4 posts - 1 through 3 (of 3 total)

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