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 all fields of two rows in few tables.

The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-

Combo box to exclude blank input

Hi
I have a combo box that displays a list of company names. Some entries do
not have a company name on the form so the textbox remains blank. Therefore
in the combo box there are alot of blanks in the list. How do I get the
combo box to just list out the company names without the blanks?
At the moment I have this:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts;
Thanks in advance
SeanThe following will exclude NULL or blank CompanyName rows:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts
WHERE
Contacts.CompanyName IS NOT NULL AND
Contacts.CompanyName <> '';
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:9930ACDB-8E70-45B8-94B1-89A8DF57B60F@.microsoft.com...
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank.
> Therefore
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean|||Isn't there a Company table that, by definition, wouldn't have empty
company names?
If not, then one should be added - basic normalization.
Sean wrote:
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank. Therefor
e
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean