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
Maybe I don't understand the scenario completely, but if the database is small, I think you could just backup the first database and restore it in a new location. This would preserve any relationships automatically.
These links might be helpful in understanding BACKUP and RESTORE:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/ms186858.aspx
Hope that helps.
-Brian
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.