2

I have created a new table using a SELECT INTO statement. I want the new table to automatically refresh if the original table changes (i.e. new rows of data added).

I have googled around but can only find solutions to refreshing a views, not created tables.

SELECT A, B, C, D 
INTO NewTable
FROM OldTable1 LEFT JOIN OldTable2

I would expect that the new table is constantly up to date with the data in the old table. Any advice would be welcome

Jimbo
  • 67
  • 9
  • 2
    You could create a trigger on the source table to apply changes to the new one but this begs the question why one would want to maintain a redundant copy to the table in the same database. – Dan Guzman Jan 03 '19 at 11:59
  • Thanks, Dan. I over simplified my example code, their are actually 3 old tables that I would like to extract fields from into a new table (which refreshes). What should I be googling to get the "trigger" info needed for this please? – Jimbo Jan 03 '19 at 12:04
  • 2
    It seems your desire is to materialize query results for a query that doesn't conform to the indexed view restrictions. The simple trigger solution would be a `TRUNCATE` and `INSERT...SELECT` to refresh the view whenever any of the underlying tables change but that isn't efficient of the tables are changed often or updated in the same transaction. Another trigger solution would be triggers that update the table based in the query semantics but that can be complex. You could instead schedule a refresh if data don't need to be updated in real time. – Dan Guzman Jan 03 '19 at 12:20
  • 2
    If the problem you are trying to solve is performance, why not create a normal view with attention to query and index tuning detail. – Dan Guzman Jan 03 '19 at 12:21
  • I have just been told to create a new table with fields from old tables and that the new table should always be up to date with the columns in the old tables. I had already created a view but they said it wasn't what they asked for. – Jimbo Jan 03 '19 at 12:38
  • 1
    Definitively the problem is that the person that has made the request don't understand how SQL Server works. @DanGuzman has told all the options (voted up) and keeping a redundant copy for the table is by far the worst option as will use more resources, has more development effort and has risk to have innacurate information. – Angel M. Jan 03 '19 at 15:38

0 Answers0