CODEWITHSUNDEEP
sqlsql-serverdatabase
otk
otk

Reputation: 411

How to create a new table, based on existing table with dynamic content, but keeping the history in the new table?

i have a table (OriginalTable) with ~350 records that have uniqueID and the records decrees/increase/update over time.

I want to create a new table (NewTable) based on these records, but keep all records in NewTable even if they drop out of the OriginalTable. This raises some question:

  1. How do i keep all records in NewTable when copying from OriginalTable ?

  2. What is best-practice for activating a daily procedure to copy content from OriginalTable to NewTable? - create a job with the SQL Server Agent?

  3. I cant see the SQL Server Agent in MS management studio, does that mean i dont have the neccessary priviliges? (It's an elastic-pool db - Azure)

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Do you simply want something like this?

insert into newtable ( . . . )
    select . . .
    from oldtables o
    where not exists (select 1 from newtable n where n.uniquekey = o.uniquekey);

Such a query can be scheduled to run periodically using a job in SQL Server Agent.

Upvotes: 1

Related Questions