I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?
We have set up a couple of SQL Server 2005 systems and I have foundthat the format of the ERRORLOG files and the SQL Agent's log filesare Unicode or some format that findstr cannot parse properly. "find"parses them fine, but it doesn't have the capabilities that I need --specifically, I can't search for multiple strings in one search.I see the checkbox on the SQL Agent's for "Write OEM File", but it isgrayed out so I am not able to try checking that. I also don't knowif that would affect the server's ERRORLOG file too or just theAgent's log file.So what am I missing? What is everyone else doing who is used tohaving scripts to parse these files looking for strings that indicateproblems? Is there a server setting that will force it to go back toa plain ANSI text file format for log files? Is that a bad thing todo?Thanks in advance for any insight,Teresa Masino
I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle( Cycle INT NOT NULL, PatientID INT NOT NULL, ALOPECIA TINYINT, Causality1 TINYINT, Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2 CREATE TABLE #Suppliers (ID INT, Product VARCHAR(500) ,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500) ,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
I'm searching for a way to compare informations from one database against another database.
E.g. i want to check if serialnumbers of my first database (eg hardware.xls or hardware.csv ) are already stored in my sql server database.
I know i can use sql querys (Joins) for that task but i don't know how i can access 2 different databases at the same time. Do i need a tool for it? Does Excel is able to compare those data? But how? I'm searching for that thread since 2 days testing with some tools without any success...
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237) INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)
--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578) INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)
--Here what I need is
--For product 701 there is one item missing ie 1237 which exists in its parent 101
--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102
--and extra item exists ie 8976 so my result table should lokk like this
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
I would like to know if there is any way to migrate third-party data mining packages with SQL Server 2005 data mining algorithms together then we can have a comparison among all of them to get the best results for training models.
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. Â used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. Â one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. Â no effect; Â the user makes their own login id but that does nt matter either.
We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously. b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
How to I compare the data between prod and dev databases. I need to create sql script to list the new data and the modified data in development tables comparing with the production tables.
was able to load data from 2005 to 2000 about 100+ tables. Now i'm only concerned about if data is the same the rows and data types (not sure if there is any difference). Any ideas how to test it? should i go row-by-row???? its over 100 tables. OH MY goood...
I've created two tables. One table (Classes) stores data about classesthat we offer. The Classes table stores the class id (classid) and themax number of students allows (maxstudents). The other table(Students) stores student data and the class they register for.When a user registers for a class, the classid column data from Classespopulates the class column in Students.I'm not sure how to count the number of students who registered forcourse X, subtract that from the max number of students in the Classestable, and display that the class if the max is reached either in awarning dialog box or as text on the page.I'm also populating a drop-down field on the registration form with theclass information from Classes. Confused yet?I don't know much about SQL or .ASP. Any help is appreciated.
Hello all,I am new to sql and have some Access experience.In sql, how do I: compare 2 identical tables, (except for data); then updatetable 1 with new data from table 2TIAJake
I know this sounds simple, but I haven't seen it in bol. I need to compare two tables, and list what rows are unique to each table. Thanks for the help!
The data looks like the following --------------------------- | PBP 20070420 2:26pm | ---------------------------
Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied.
I need to find all the dates that are within the last 10 months. How do I perform this task?
Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need):
I have 2 tables.
Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records.
Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers.
I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3.
Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out.
I have a billing database with patient names in it. I received a tab delimited file from insurance plan of our roster of assigned patients.
I now want to compare the insurance roster to our database to see who is missing.
The roster is layed out like this (info jumbled to protect privacy): Eligibility List Sample Last Name First Name Date of Birth Gender Insured ID VW Acct # ALLEN CARRIE A4/16/1939FDH36664A572576-02 BAKER AMBER S11/24/1956FFXI2824C596439-02 BARKLOWLOREN R12/15/1956MKVF0092A588878-01 BRENNANPATRICIA A 1/14/1959FFXI8763A549675-02 BROWN MARTHA E8/14/1967FBD65508A366963-02 CALDWELL MICHAEL V 12/19/1969MLR500N2J595087-01 CLARK CYNTHIA A4/24/1971FVO600M8O596011-02 DEMPSTER SCOTT A 2/21/1976MCC85242A573371-01 DUNNE ANNETTE M10/26/1976FAE88375D598423-02 DUNNE CHRISTOPHER M 8/1/2021MBV81536A598423-01
I have loaded the text into an Excel Spreadsheet to work with it.
I was able to query our patient profile data base to get people with this insurance plan...but of course the data is never an ideal match.
For instance, some of the roster patients above have Middle Initials Concatenated to the First Name. In my database it is a mixed bag of missing initials, initials concatenated to first name or initials in separate Middle field. Thus a strict match on name is not going to work.
Date of Birth should hopefully be valid between both data sources.
Probably the best source of data to validate on would be the VW Acct# as I trust this to be the same in both sets of data. However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02. Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info.
An example of the query I was able to pull from the patient data base is as follows:
LastFirstMiddleDate of BirthGenderNotes ClarkLawrence J9/7/1955MVital Works ID: 7575-01 ClarkKayleeann NULL1/3/1955FVital Works ID: 7575-02 ColeCodyNULL8/19/1948FVital Works ID: 8771-02 snt ref req to ohms for impact appt tbs Sent ref req back to ohms for Impact-DX. CreaseyWadeL7/9/1988FVital Works ID: 602659-02 KennyRoyJ2/27/1953FVital Works ID: 602679-02 UttJannieC4/11/1984MVital Works ID: 602715-01 WestAliciaG9/9/1992MVital Works ID: 602736-02 WrightMinnieO2/17/1991MVital Works ID: 602736-03 YankeeDonald E10/27/1996MVital Works ID: 602762-03 YankeeStephana A4/4/2001FVital Works ID: 602762-04
How could I now construct a query that would tell me what patients were in the eligibility roster that didnt have a match in the patient database?
I would like to then save that to Excel or somewhere that I could print it out from so I can have someone up date the database.
ALTER TABLE xxgfs_gen_text_lookups ADD CONSTRAINT xxgfs_gen_text_lookups_uq_1 UNIQUE (lookup_type,region_code,nongfs_value1,nongfs_value2,nongfs_value3);
i have some data in excel which i have uploaded using sql*loader using control card.Now i want to compare the data in both tables having same table structure only
do any body having idea how to compare the data using storeprocedure. thanks in advance
Does anyone have an opinion on specific “data comparison tools�?
We are looking for something to use in our test or dev environments that will be able to compare snaps shots of the data in a database before verse after a test event.
We have been able to record and compare data in specific tables but are learning that other tables were also being changed that we didn’t track. We want to be able to see all changes to a database.
I am trying to determine the changes an application makes to a database.The plan is to copy the existing schema (active) to a reference schema, runthe application and then diff the table data between the reference and thea active schema. I have found one software vendor who has a tool to dothis, but it will only do one table at a time (interactively); I have morethen 300 and will run this a few times.One other way of determining the changes, I guess, would be to log all sqlstatements (in order), but I don't know how to do this (either).Any pointers would be greatly appreciated.Leo
Using SQL data compare i get the following error message:Could not allocate space for object '(SYSTEM table id: -701903460)' indatabase 'TEMPDB' because the 'DEFAULT' filegroup is full.The comparison is being run on one server between to databases. It hasbeen run several times but each time we get that error message, thetable ID number tends to change each time. We also had a problembefore where the 'TEMPDB' did not have enough space in its log filebut that was fixed by allowing the database to expand in size.
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.
The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.
My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.
I'm very new at using SQL and my knowledge of the query syntax is very basic.
We will need to routinely import only changed data from an Oracle data base into a SQL database. So we need an agent that will 1) Compare data in both databases (From disparate tables) 2) Import only that which is changes or new.
I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis. I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution. Can someone point me in the right direction? Thanks.
