Hello

I have 2 databases, one is like a backup for the other.
So they have identical tables: Alarmi, which has columns: OBJEKT, OPIS, VREDNOST, START, E_A, UPORABNIK

I want to compare those two tables and copy only new entries (rows) to the backup database.
How can I do that? Can it be done like a stored procedure?

Thank`s in advance for the help.

Have a nice day,
vedro

Yeah you could use TSQL, but it could get nasty if you have dependencies like referential integrity (you'd have to consider the order of the synchronization of tables).

Look into the EXCEPT clause if you have SQL Server 2005 and above. If you got the cash, I would recommend RedGate's SQL Data Compare to automate it. The trial has a 14-day, fully functional version. It's saved my bacon many times.

Hello

Thank`s for the reply.
Yes, I`m using SQL Server 2005 Free edition.
RedGate's SQL Data Compare would do the trick but it is expensive.
You have mentioned EXCEPT clause.
How to use this clause for my situation?

Thank`s

Have a nice day,
vedro

Well, with EXCEPT, it would work like this. I'm using

INSERT INTO #table1
  SELECT  *  
  FROM    #table2 -- latest data table (we'll be copying from here)

  EXCEPT

  SELECT  *
  FROM    #table1 -- backup table (has old data)

So that would first check if there's new records from #table2 that are not in #table1. Then it would put those records in #table1. You'll also have to query the sys.tables system view to get all table names. I would loop through all the tables and execute that snippet code. I would consider dynamic TSQL. You can write them out manually if you don't want to, but it will depend on how many tables you have.

Also there a few issues:

1. Are you using referential integrity?

2. The tables that you're comparing - for the tables that have "new" data - is that "new" data coming from just INSERT statements? If they are "new" because of UPDATE statements, then you have to take that into account. The snippet that I show here - if you have two records from each database, different because of one column, then it'll be consider as "new" data. Not sure if this is what you want.

CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. Double check.

Check also if you have TableDiff.exe in your directory. Not sure if that comes with Express version of SQL Server: http://weblogs.sqlteam.com/mladenp/archive/2007/03/03/60125.aspx

Hello

@samaru
Thank`s again for the reply. At the moment I cannot try anything. I will go to the firm and try there, where I have the installed applications.
I will try with EXCEPT clause and with TableDiff.exe
I`ll report how it will go.

The thing is that SQL is not my field of expertise - I am studying electrical engineering - Automation. And I have one project which needs database knowledge (with SCADA) so I am learning this as I go... And I am willing to learn as much as I can.

@Brian

The database is quite large and it is not very optimized. The project was started before I joined so I have to try to optimize it and resolve few issues.

Thank`s

Have a nice day,
Vedro

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.