Identifying Differences Between Two Records
Feb 8, 2007
I am trying to test some data handling between two different versions of an application.
I have restored the database schema twice, once as DB_old and once as DB_new.
I import a transaction using the new application into DB_new and I import the SAME transaction into the DB_old using the old version of application.
I then have to eyeball the data in SQL Query Analyzer to try to identify problems where the fields have received different values.
I have done this by running a select statement twice telling it to use both of the databases and then viewing it in two grids. There are a lot of columns so I have to do a lot of scrolling across the screen to do the comparison, and since the view is in two separate grids I have to hop back and forth and click the scroll bars, etc.
It seems like there has to be a better way. I don't suppose there is a way to lock the two grids so they both scroll together is there?
I was thinking maybe I could insert each of the selects into a temporary table and then do some kind of comparison to identify which values were different in each column. Some of the columns will have differences, like the timestamp, but if I could somehow identify which columns were different then I could eyeball them to identify which of those were okay to be different and which of them were actually bugs from the changed application version.
I have no idea how to identify those individual columns with different data values or even where to start.
Just so you understand better what I am doing now here is the query I am running that I then eyeball:
use DB_new
select * from claim where claim_id = 35144
use DB_old
select * from claim where claim_id = 35144
Thanks for any ideas.
View 7 Replies
ADVERTISEMENT
Sep 26, 2014
I have this query below that I created to do a count, but I don't think this is what I needed.
I need to find the duplicates. Example, if
CLI_ID1 12345 has 4 CLIP records, each CLIP record should have a different CLIP rank. I need to find scenarios where 2 (or more) of the CLIP records have the same CLIP RANK. If there are duplicate CLIP_RANKs within the same CLI_ID,
Select Distinct
cli_id1, count(clip_rank) countrank
FROM impact.dbo.CLI
LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1
left join
impact.dbo.clip ON cli_id1 = clip_id1
Where (clio_trm = '' or clio_trm = NULL or clio_trm is null)
group by cli_id1
order by cli_id1
View 1 Replies
View Related
Mar 17, 2014
I have a series of records based on empid where I want to identify the empid that may have discrepancies listed. I have some empids that are listed more than once and have different DOB's. In the example I am trying to Create a DOB_ERROR column and either say yes if the DOB doesn't match the other records in the file with the same empid.
SELECT
Empid,
DOB,
CASE WHEN DOB = DOB THEN 'No' ELSE 'Yes' END AS DOB_ERROR,
City,
St,
Gender
FROM Emp
WHERE EMPID IN
('12335', '23456', '545432','231245')
View 3 Replies
View Related
Oct 8, 2015
any useful SQL Queries that might be used to identify lists of potential duplicate records in a table?
For example I have Client Database that includes a table dbo.Clients. This table contains various columns which could be used to identify possible duplicate records, such as Surname | Forenames | DateOfBirth | NINumber | PostalCode etc. . The data contained in these columns is not always exactly the same due to differences caused by user data entry; so some records may have missing data from some of the columns and there could be spelling differences too. Like the following examples:
1 | Smith | John Raymond | NULL | NI990946B | SW12 8TQ
2 | Smith | John | 06/03/1967 | NULL | SW12 8TQ
3 | Smith | Jon Raymond | 06/03/1967 | NI 99 09 46 B | SW12 8TQ
The problem is that whilst it is easy for a human being to review these 3 entries and conclude that they are most likely the same Client entered in to the database 3 times; I cannot find a reliable way of identifying them using a SQL Query.
I've considered using some sort of concatenation to a new column, minus white space and then using a "WHERE column_name LIKE pattern" query, but so far I can't get anything to work well enough. Fuzzy Logic maybe?
the results would produce a grid something like this for the example above:
ID | Surname | Forenames | DuplicateID | DupSurname | DupForenames
1 | Smith | John Raymond | 2 | Smith | John
1 | Smith | John Raymond | 3 | Smith | Jon Raymond
9 | Brown | Peter David | 343 | Brown | Pete D
next batch of duplicates etc etc . . . .
View 7 Replies
View Related
Feb 9, 2015
I would like to calculate the date difference between two records:
IDStartEnd
12/9/15 13:28:412/9/15 13:28:46
22/9/15 13:29:032/9/15 13:29:12
32/9/15 13:29:172/9/15 13:29:21
42/9/15 13:29:272/9/15 13:29:31
So I need to find the difference between two consecutive records and put the result in a new column called Downtime. For example:
Record 2 Start (2/9/15 13:29:03) - Record 1 End (2/9/15 13:28:46) = 0:00:17
Record 3 Start (2/9/15 13:29:17) - Record 2 End (2/9/15 13:29:12) = 0:00:05
Record 4 Start (2/9/15 13:29:27) - Record 3 End (2/9/15 13:29:21) = 0:00:06
and so on…
Also what do I do about the 1st record since there is no previous record to subtract from?
So far I have this code in my query to generate my table: SELECT Start, End FROM group_table3
My desired output should look like this:
IDStartEndDowntime
12/9/15 13:28:412/9/15 13:28:46#VALUE!
22/9/15 13:29:032/9/15 13:29:120:00:17
32/9/15 13:29:172/9/15 13:29:210:00:05
42/9/15 13:29:272/9/15 13:29:310:00:06
View 4 Replies
View Related
Aug 20, 2001
Hello,
I have recently started working on a OLAP application. The database is around 35 gig, with over half of that space being taken by the indexes. What has happened is previous developers have added indexes to attempt to improve the performance of a select statement (for reporting). Some of the indexes work, but many do not.
I would like to drop the ineffective indices. What I would like is a way to find if indexes are being used, preferably by object name. Is there a third party vendor that can monitor objects and how much they are used? Is there another way to do this using SQL?
Thanks in advance,
Jim
View 1 Replies
View Related
Sep 5, 2000
Hi,
I need to delete ONLY the duplicate rows from a table..Can anyone suggest me a way to do that thing..i used the query..
"Delete from <TableName> where SlNo IN ( Select SlNo from <TableName> group
by SlNO having Count(*) > 1)"
The resultant is all the original rows also getting deleted with the duplicates..anyone please help me out..
Thank you in advance
Rani
View 5 Replies
View Related
May 20, 2004
How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.
Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().
Can anyone help ?
View 7 Replies
View Related
May 6, 2008
Hi,can anybody tell me how to identify all the databases from all the servers that are being accessed by a particular group
View 11 Replies
View Related
Nov 22, 2006
Hi
Is there any way to Identify a Deadlock using the Enterprise Manager.
Someone told me that the Red Icon on the Lock/Process ID (Spid) mean a Dealock ... is that True ???
View 3 Replies
View Related
Mar 6, 2008
Hi,
Can we define a non-identifying relationship between 2 tables in MS SQL? If yes, what will be the syntax based on what I have:
ALTER TABLE PM_VARIABLE_BIN
ADD CONSTRAINT PM_VarCoef_PM_VarBin_FK1 FOREIGN KEY (MODEL_ID,VERSION,VAR_ID) REFERENCES PM_VARIABLE_COEFFICIENT(MODEL_ID,VERSION,VAR_ID)
go
View 1 Replies
View Related
Mar 14, 2006
Some columns in transaction tables are "mandatory fields" on the dataentry screens, and as a result tend to accumulate junk entries, wherethe user puts something, anything, in the window in order to get theGUI to accept the screen. This filler isn't as elaborate as LoremIpsum, but more likely characters from adjacent keys on the second rowof the keyboard, like "lkjkljl". This non-data gets in the way ofapplications that use the data. I wonder if there is a way torecognize and ignore this stuff -- I would assume it's a well-knownproblem, but I haven't found any literature on it. Any pointerswelcome. And sorry if this is off-topic for a DB group.Thanks,Jim Geissman
View 2 Replies
View Related
Jul 20, 2005
Hi AllThe problem:!!I want to create a temp table which is common between different usersso that each user could select his rows and print them withoutprinting others selection.All clients use the same connection string.Is there a way to identify users?I think it's possible to delete rows of a user when he logs out theprogram.Do I think right?thanks very muchHafez
View 1 Replies
View Related
Mar 23, 2007
Has previous work been done on this? Is their a library one can download? Here's the problem. In an aricle a person or author may make a statement about a subject or a person. I am making a database on this.
For example, here in Israel, PM Olmert may make a statement on the teacher's strike or on Abu Mazen. The article may say, speaking of Mazen, Olmert said such and such. PM Olmert said, "xxxxxxx......." with the previous material making it clear whathe was speaking of.
Right now I have ugly code. Is there a neat way of doing this?
If this is the wrong forum, can somebody direct me to the right forum?
Thanks.
Dennist
View 3 Replies
View Related
May 4, 2015
Have a table showing info like:
Region_ID
Branch_ID
Customer_ID
Transaction_ID
In this regard need to create a view to reflect the info in the order
Region_ID (Ascending)
Branch_ID (Asc)
Customer_ID (Asc)
Transaction_ID (Asc)
But, at the same time, also want to include two additional columns identifying or even inserting 1's against each of the first Customer_ID considering the first two columns, and the second one on the single column itself?In other words consider the following sample, how to add 1 to the last two columns where either the Customer appears for the first time in context with the Region and Branch Fields or to be appearing on it's own for the first time:
Region
Branch
Customer
Transaction
Required_A
Required_B
[code]....
View 3 Replies
View Related
Jun 15, 2007
Hell Sir,
I am using repeater control to show the result after search .and using checkbox control in itemtemplate row .After searchresult i am facing a problem in identifying the checked checkboxes in the itemtemplate of repeater control .
Please provide appropriate solution ....
thanks for ur attention...
View 3 Replies
View Related
Mar 9, 2001
Hi everybody,
I'm migrating a table that has above 20,000 records and lot of duplication.Let's say an Employee table with multiple records having slight
diference in the EmployeeName field.Now nobody would like to sit and manually identify them with such hugh number of records.
Is there any way which would help me identify most of them and
reduce the redundancy.
Thanx
Aby...
View 2 Replies
View Related
Mar 1, 2004
Hi,
Can anyone tell me how to find out on what port number a paticular sqlserver named instance is running...
thanks..
View 5 Replies
View Related
Feb 23, 2005
We would have come across locks on rows, tables etc. How do we handle it
through code (SP)!!!
A job is executed at 12 pm midnight on a table. This table exists on an
24X7 accessed (users will be access the table anytime in the day/night)
database. We need to execute this job if this table is not locked at 12 pm.
Can we have the solution for the listed are two scenarios:
1. You have a normal user and do not have system privilege.
How would you design the database(Table)? Is it possible?
2. You have all the privileges on the system tables.
What would be the sql script to help us identify the lock? How will
it be used in your code? Or is there exception handling !!!
View 1 Replies
View Related
Jun 23, 2008
I have table <contact>
I wand to higllight the duplicate student .I am considering two parameter Name and Birthdate to identify such records even though the probablity of two different student having same name and same DOB is very less.Margin for bad result is relatively low for me.
fisrtname middlename lastName birthdate
------------------------------------------------
peter jones clooney 29/06/2008
peter jones clooney 29/06/2008
both are actually same student but different from database point of view.
View 2 Replies
View Related
Nov 17, 2006
Hi
Can we identify the reason for a Deadlock , after it has passed. I mean can I get to knw the reason as to why the deadlock occured yesterday on my production box.
I had not left the profiler on ... neither did I send the dead lock erorrs to the SQL error logs using the DBCC TRACEON (1204, 3605, -1)
Is there some way we can figure it out .
Please help.
View 2 Replies
View Related
Jun 4, 2007
I'm getting the following from DBCC CBECKDB (there are other errors in there, too but this is the one that concerns me at the moment):
Msg 8939, Level 16, State 42, Line 1
Table error: Object ID 1227270540, index ID 255, page (1:5359776). Test (m_slotCnt <= 1 && m_type == TEXT_TREE_PAGE) failed. Values are 8 and 1.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 1227270540, index ID 255, page (1:5359777), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 8186 and 220.
Msg 8941, Level 16, State 102, Line 1
Table error: Object ID 1227270540, index ID 255, page (1:5359778). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 7, offset 0x1 is invalid.
Msg 8942, Level 16, State 101, Line 1
Table error: Object ID 1227270540, index ID 255, page (1:5359778). Test (sorted[i].offset >= max) failed. Slot 0, offset 0x7 overlaps with the prior row.
CHECKDB found 0 allocation errors and 4 consistency errors in table '(Object ID 1227270540)' (object ID 1227270540).
However...
select objectproperty(object_id('1227270540'),'IsTable')
...returns null. I've checked the sysobjects table for this ID, and it doesn't show up. Any ideas on how I can identify what this object is? Let me know if you want more info. This is SQL 2000, sp4, the DB is about 60GB and it's the only one on the system having issues. The issue has been around for a while (more than a month), and is consistent. I can post all of the DBCC output if you really want/need it. I was just hesitant to do so since it's so huge.
Boy, 4th day on the job and the hits just keep coming. :-) (Not complaining. I'm learning a lot!)
UPDATE: 2007.06.04_15:44 - Funny: When I ran dbcc checktable(CheckItem), I got all of the error messages displayed below, both for the mystery object ID and for the CheckItem table. I guess that's solved, but I'd still like to know if it's possible to have identified this another way, if anyone has time. Thanks.
-D.
____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide
View 4 Replies
View Related
Mar 11, 2006
I need help flagging duplicate records in ome tables I have.For example if I have Table1 which conatins Field1, Field2 and Field3like belowField1 Field2 Field3 Field4Paul 18 Null NullPaul 18 Null NullJohn 19 Null NullHow would I;1. put a 'Y' in Field3 to mark the two records which are duplicates.2. put a 'Y' in Field4 to mark ONLY ONE of the duplicate records.Regards,Ciarán
View 2 Replies
View Related
Jul 20, 2005
I have inherited support for a database with many used tables. Thereisn't any documentation on what is used or dead. I was hoping to runtraces and capture the objectid but the property doesn't work thatway.Any good ideas would be appreciated.
View 3 Replies
View Related
Nov 14, 2006
To All:
I'm importing a data file of 600,000 records. Some records contain bad data.
What's the best way to identify the text file row and field that caused my package to bomb AT THE TIME IT bombs?
Thanks,
r
View 1 Replies
View Related
Feb 8, 1999
When distributing a SQL Server database to customers we need to be able to
tell which version of a stored procedure is currently installed on the user's machine. Does 6.5 or 7.0 provide any way to monitor versions like this?
View 1 Replies
View Related
Nov 12, 2002
Hi,
i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.
Skol,
Peter
View 4 Replies
View Related
Feb 9, 1999
When running the DBCC newalloc command I get some errors like the following:
"extent 51400 is in the wrong segment"
"chain processed with bad segment for object 448004627"
I've checked the system tables and system stored procedures and see no reference to page numbers being mapped to segments or identifying which extents are associated to which tables. I'm trying to determine which pages are associated with these extents so that I can print them to view the content. Any ideas how to determine the page numbers associated with extents and which extents are associated with each table?
View 2 Replies
View Related
Jul 25, 2001
Hello,
I monitor a few "perfmon" counters which includes under the "system" object, "bytes transmitted/sec" and "file read bytes/sec". Every once in awhile, these counters will skyrocket, which can also be verified by the hard drive lights flickering like mad.
The only software installed on the machine is SQL Server 2K.
I was wondering if anyone knew how I could monitor within SQL 2K what process or user is using all of these cycles. If anyone could shed some light on this it would be greatly appreciated. Specifically, I would like to find out which database/query is doing this to minimize in the future as this affects all of the other connections.
Thanks in advance,
Brent.
View 1 Replies
View Related
Sep 25, 2001
Can any one recommend an approach for identifying the frequently accessed tables and files in a SQL/Server application? Particularly, if the database is stored on raw partitions instead of FAT or NTFS. Thanks.
View 3 Replies
View Related
May 5, 2008
Hi-
I have installed a SQL 2005 software and created 4 instances and 4 databases on each server namely , Adventureworks , sample , testdb1, testdb2.
After creating these many databases , i was very confused to find out the actual database files in Programfiles directory as each instance was created in a separate directory namely MSSQL.1 , MSSQL.2...etc.
how do i identify the correct database path for sample database in instance3.
is there any system tables or stored proc that i can user to identify this path.
tanks
View 4 Replies
View Related
Mar 6, 2014
I have a snapshot table of about 15 million records in the form of:
InvoiceIDLineItemIDSnapshotDateAmount
1 1 20140101 12
1 2 20140102 14
1 3 20140103 17
2 1 20140101 10
2 2 20140102 5
1 2 20140105 15
1 3 20140105 20
I want to create an additional column called Current as shown below:
InvoiceIDLineItemIDSnapshotDateAmount Current
1 1 20140101 12 1
1 2 20140102 14 0
1 3 20140103 17 0
2 1 20140101 10 1
2 2 20140102 5 1
1 2 20140105 15 1
1 3 20140105 20 1
How can we write a query to achieve this while keeping in mind:
- We do not want to do unnecessary record lookups and Updates
- We only update records that corresponds to new entries. For example, we should not touch the record for InvoiceID = 2 in the above example
View 6 Replies
View Related
May 7, 2014
I had a database in suspect mode this morning.
Fixed it by:
-placing in emergency mode.
-set single_user mode
-dbcc checkdb(N'dbname', REPAIR_ALLOW_DAT_LOSS)
-Rebuild the log.
-bring online.
DBCC Checkdb returned a bunch of errors:
Extent (3:394032) in database ID 7 is allocated by more than one allocation object. The error has been repaired.
Msg 8913, Level 16, State 1, Line 2
Extent (3:394032) is allocated to 'GAM' and at least one other object.The error has been repaired.
--Lots of these
Repair: Deleted off-row data column with ID 562825134080, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 28.
Repair: Deleted off-row data column with ID 562825199616, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 29.
Repair: Deleted off-row data column with ID 562825265152, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 30.
Lots of these:
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8977, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044088320 (type In-row data). Parent node for page (3:394009) was not encountered.
The error has been repaired.
[code]....
View 9 Replies
View Related