Comparing Table Records

Hi everyone,

I’m looking for any recommendations or ideas for achieving a task I am currently doing in a much more efficient way or form. I am currently running a process that takes data in an Excel Worksheet and populates a SQL Table. Each record within that SQL Table is then read by another system to perform workflow related tasks. The problem I am having is reading each SQL record all the time, looking for delta changes. As you can imagine, this can be pretty time consuming if each record contains many column values with thousands of records in the DB.

I’m using a DTS Package to transform the data from Excel to SQL. The package is extremely dumb, in a sense all it does before doing a bulk import is running a “TRUNCATE TABLE [table]” command against the target to clear all the values before reloading.

I created a trigger in my primary table, which listens for delta updates. Upon a record update, it makes the record change within the primary table, additionally writing that record value to another Delta_Table within the Database. The benefit there is my outside system only needs to read the delta table to make updates rather than the primary which eliminates the need to parse through each record one by one, all the time. As you see, this cuts the read time from my outside system more than half.

I guess what I’d like to do is see if there is a better way to load the table without having to truncate all the records. Ideas?

Thanks Everyone!

Can you use Integration services. If so there is a slowly changing dimension component that you set to compare the loading data against the stored data and then decide what to do.

You could load your data into another table then do the following,

DELETE any records that exist in your target table that don't exist in the laoded table

UPDATE any records that exist but are different

INSERT any new records

you can then have a trigger that populates your diff table.

I prefer the Integration services option, I believe something similar is available in DTS

Comparing changes between two SQL Server installations?

Hi all,
I've just inherited an unusual problem and I need some recommendations and
advice.
My employer has a SQL Server 2000 installation on which they run their whole
operation, with roughly 100 tables, a few dozen stored procedures and views,
and various user logins. Prior to his departure, our most recent DBA did a
good deal of new SQL Server development in our development database
environment. Unfortunately, it appears he did *everything* through the
Enterprise Manager GUI, leaving no SQL scripts of any of his changes. Now my
employer wants me to figure out (A) what changes he made, and (2) generate
SQL script for all of those changes beforehand. That way, we can make those
same changes in production.
Other than manually comparing every single table, view, stored procedure,
and login, is there any tool (built-in or 3rd party) that will allow me to
reconcile the objects between two wholly separate SQL Server 2000
installations?
Essentially, to put it another way, I need to "diff" the whole schema and
any stored procs and generate files to allow me to implement those changes
against production. And, ideally, I need scripts allowing me to rollback
changes as well.
If not, is there at least some sort of source-control tool for SQL Server we
could try to use in the future?
Thanks!
--Phil
Philip P. Obbard wrote:
> Hi all,
> I've just inherited an unusual problem and I need some
> recommendations and advice.
> My employer has a SQL Server 2000 installation on which they run
> their whole operation, with roughly 100 tables, a few dozen stored
> procedures and views, and various user logins. Prior to his
> departure, our most recent DBA did a good deal of new SQL Server
> development in our development database environment. Unfortunately,
> it appears he did *everything* through the Enterprise Manager GUI,
> leaving no SQL scripts of any of his changes. Now my employer wants
> me to figure out (A) what changes he made, and (2) generate SQL
> script for all of those changes beforehand. That way, we can make
> those same changes in production.
> Other than manually comparing every single table, view, stored
> procedure, and login, is there any tool (built-in or 3rd party) that
> will allow me to reconcile the objects between two wholly separate
> SQL Server 2000 installations?
> Essentially, to put it another way, I need to "diff" the whole schema
> and any stored procs and generate files to allow me to implement
> those changes against production. And, ideally, I need scripts
> allowing me to rollback changes as well.
> If not, is there at least some sort of source-control tool for SQL
> Server we could try to use in the future?
> Thanks!
> --Phil
There are a number of available products onthe market that perform
schema comparisons and produce the necessary DDL. We offer Speed Change
Manager from the Imceda web site. You can try it for 14-days.
David Gugick
Imceda Software
www.imceda.com
|||you should take a look at DB Ghost which can help you manage all your changes
to your schema and static data allowing you to store and use the sql
defintions of all objects and static data within your source control giving
you total automated change management with the auditability features of your
source control so this thing will never happen again and you can quickly
assertain exactly what has happened.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Philip P. Obbard" wrote:

> Hi all,
> I've just inherited an unusual problem and I need some recommendations and
> advice.
> My employer has a SQL Server 2000 installation on which they run their whole
> operation, with roughly 100 tables, a few dozen stored procedures and views,
> and various user logins. Prior to his departure, our most recent DBA did a
> good deal of new SQL Server development in our development database
> environment. Unfortunately, it appears he did *everything* through the
> Enterprise Manager GUI, leaving no SQL scripts of any of his changes. Now my
> employer wants me to figure out (A) what changes he made, and (2) generate
> SQL script for all of those changes beforehand. That way, we can make those
> same changes in production.
> Other than manually comparing every single table, view, stored procedure,
> and login, is there any tool (built-in or 3rd party) that will allow me to
> reconcile the objects between two wholly separate SQL Server 2000
> installations?
> Essentially, to put it another way, I need to "diff" the whole schema and
> any stored procs and generate files to allow me to implement those changes
> against production. And, ideally, I need scripts allowing me to rollback
> changes as well.
> If not, is there at least some sort of source-control tool for SQL Server we
> could try to use in the future?
> Thanks!
> --Phil
>
>

Command line qeury of service

I have been looking at SC.exe from the resource kit to query sql server
services from command line. Anyone have any recommendations other than this
to do so.
I was looking to query all my sql servers to check services and output to a
file.
Russ,
How about VBScript and WMI? Or VBScript and SQL-DMO?
HTH
Jerry
"RussN" <RussN@.discussions.microsoft.com> wrote in message
news:EEA1A919-672C-441F-8F4D-9838F67650FE@.microsoft.com...
>I have been looking at SC.exe from the resource kit to query sql server
> services from command line. Anyone have any recommendations other than
> this
> to do so.
> I was looking to query all my sql servers to check services and output to
> a
> file.

Command line qeury of service

I have been looking at SC.exe from the resource kit to query sql server
services from command line. Anyone have any recommendations other than this
to do so.
I was looking to query all my sql servers to check services and output to a
file.Russ,
How about VBScript and WMI? Or VBScript and SQL-DMO?
HTH
Jerry
"RussN" <RussN@.discussions.microsoft.com> wrote in message
news:EEA1A919-672C-441F-8F4D-9838F67650FE@.microsoft.com...
>I have been looking at SC.exe from the resource kit to query sql server
> services from command line. Anyone have any recommendations other than
> this
> to do so.
> I was looking to query all my sql servers to check services and output to
> a
> file.

Command line qeury of service

I have been looking at SC.exe from the resource kit to query sql server
services from command line. Anyone have any recommendations other than this
to do so.
I was looking to query all my sql servers to check services and output to a
file.Russ,
How about VBScript and WMI? Or VBScript and SQL-DMO?
HTH
Jerry
"RussN" <RussN@.discussions.microsoft.com> wrote in message
news:EEA1A919-672C-441F-8F4D-9838F67650FE@.microsoft.com...
>I have been looking at SC.exe from the resource kit to query sql server
> services from command line. Anyone have any recommendations other than
> this
> to do so.
> I was looking to query all my sql servers to check services and output to
> a
> file.