I have got Table A in database DB1 and Table B in database DB2 with same table structure. I need to compare the data in table A and Table B and add the missed records in Table B.
Can anyone help me on how to do this in a SSIS Package?
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!
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
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.
I'm trying to compare tbl_email2 (email filed) with tbl_email1 (email field) if the record exist it it does nothing if not it adds the email field in tbl_email1 the result would be
I have 2 databases( "A" + "B") with identical number of tables and identical number of records for each table. There are also identical number of fields pre record per table. Table A has had the sensitive data within the fields scrambled.
I need to know if there is a way to read down each DB table by table, record by record, field by field and compare the data values. If they are different I need to output the Field name, the data value and the Table name from the Scrambled Table (lets say its "A").
I try to compare and select data from table that which not in other table
Select AAA_Id ,BBB From dbo.Table1 a Where AAA_Id not in(Select b.AAA_Id From dbo.Table2 b, dbo.Table1 a Where b.AAA_Id = 11)
But when I modified it like:
Select AAA_Id ,BBB From dbo.Table1 a union Select 0 ,'Select' From dbo.Table1 a Where AAA_Id not in(Select b.AAA_Id From dbo.Table2 b, dbo.Table1 a Where b.AAA_Id = 11) Order by 2
Hey all, got abit of a problem. Ive got a database where ive got 2 tables. I want to create a command which selects * from table 1 and if the same values are in table2 - it updates otherwise adds. Ive got a loop so i can run through the data records But i dont know how i check 1 objrs against another within an sql statement?? please look at coding example below:
Im calling my data from the 2 tables using select commands- which is ok.- uses objrs1 and objrs2 Then im running my loop to check if data from table 1 is in table2:
Dim Q For J = 1 to iRowCount2 Q= Q+1 Call select2Cmd( ) response.write(Q) response.write("</br>") Next response.end
Heres my sql statement which checks this: sub select2Cmd( ) '======================================================================================== SET objrs5= SERVER.createObject( "ADODB.recordSet" ) dim sqlStatment
sqlStatment = "SELECT * FROM [table2] WHERE [Name] = objRs2(Name) " objrs5.OPEN sqlStatment,objConn2,1,3 '======================================================================================== end sub
I want to check the field [Name] in table2 with data from table1 and then loop to the next record. How do I do this? Please note the bold part is what im stuck on. Thanks
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
I need to compare two identical tables and return the rows that are different,they are not having any primary key. Anyone to help with the query? Thanks.
Is it possible (easily) to compare two identical tables (one from server1 'SQL7', one a download from an Oracle DB into SQL7) to see if the Oracle table has changed, what is the changed row and place said row into another table to do some other process on those changed rows?
Need help with the structuring of the following query: I've got 2 tables nl :tableA, tableB I need to do a query to determine wat DISTINCT values of a column(cIdNo) in tableA is not present in column (bIdNo) in tableB.
tableA contains Id numbers of people who still has to pay their bills, and tableB contains Id numbers of people who already paid. So I want a query to determine who still needs to pay.
I've tried the following but doesn't work, not to sure if it's the right wayto do it:
PHP Code:
SELECT distinct(cIdno) as IdNumber FROM tableA LEFT JOIN tableB ON tableA.cIdNo != tableB.bIdNo
I have two the same structure tables. One is the source table (table1), another one is the destination table (table2).
Now I want to compare these two table row by row.
For example,
If table1 row1 is the same as table2 row1, go to next If table1 row2 is not the same as table2 row2, update table2 row2 to make it the same as table1 row2. If table1 row3 is not existed in table2 row3, insert table1 row3 into table2 blank new row.
Table 1 - Is named LIVE Table 2 - Is name EOM (End of Month)
The 2 tables have these fields MEMBER STREET
I want to build a query that will return any member who has changed their address (ex: member's eom address is "123 main st", and now their live address is "345 apple st")
But I do not want to display any addresses that are different because that member wasn't a member last month (not in the eom table)
Note: Member Numbers Never Change, only addresses will.
hi, I'm pretty new to ssis so this may be an absolute beginner question but i couldn't find a solution yet. the task is to compare data from tow tables which are of the same structure. table1 contains data before some operations and table2 contains the results. now i have to find out which datasets have been changed, which one are new and which one are gone.
i've done some googlin and found this TableDifference http://www.sqlbi.eu/Projects/TableDifference/tabid/74/Default.aspx component, but to me it seems to be kind of buggy, so I had the idea of trying to set an sql statement on both ole db sources where i tried to select only the datasets that are not contained in the other table. so i tried something like this:
select * from lib.table_1 except select * from lib.table_2
but this doesn't work either. so i would be really thankful if someone could help me.
Can anyone give me the query that I can use to to compare the data between two similar tables.Data from col1,col2,col3,col4 needs to be compared. Col1 is primary key in both the tables.It should return with zero rows if they are same. Thanks for any help.
I have 2 tables , table 1 and table 2. Exact copies of some records from table 1 also exist in table 2. What i need to do is display records that exist in table 1 but do not exist in table 2.
I am trying to write a query to compare the same column in each table with "not equal" expression.
My query is like this:
select tableOne.empl_ser_no from tableOne, tableTwo where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)
I am getting the following message from SQL Server:
Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression. Command has been aborted.
I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.
similarly if First name matches and dob matches then @log is 'LN not match'.
And in case all three match it should show 'match'as log value.The query I use is a s follows:-
Code: USE testing GO DECLARE @NR int DECLARE @log varchar(200) SELECT @NR = COUNT(*) FROM a WHILE @NR>0
I need to compare if two developers did the job correctly and created identical tables.
The problem is more complex, but I will try to solve it somehow if I solve the problem of comparing two tables (let them be in different SQL Server 2008 databases) and their properties. No data needs to be compared.
I have 2 tables. They both have the same fields [OFO_Code] and [Description]
Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.
I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.
Their are 1417 items in Table 2 and 77000 items in table 1.
There are NO errors when executing, just no update
Here is my code so far:
Code: Update Qualification SET [Description] = B.Description FROM Qualification A INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns 'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
I am new to writing the queries in SQL.I want to write a query which will compare the data of two tables which are resides in DEV server and PROD Server.For the conncetivity purpose we are creating the DB link between DEV and PROD server.query to compare the data of table in DEV and table in PROD
I have a database I want to query. It is a medical billing system. I basically want to compare a list of Names and Birthdates in an Excel spreadsheet to the table of patients in the database that have that insurance type and return only the rows from the spreadsheet that don't exist in the database.
This is an eligibility list, so we need to find those that don't have this insurance set up for them in our billing system and update their records.
We will probably want to do the update manually so I don't mess it up too bad , but would like to zero in on who needs the updates rather than having to look at every single record.
Any ideas on how to approach this?
Would this be a join, or would I need to do a select from table 1 where pt/insurance not in select pt/ins from table 2?
ans writes "I’m using SQL Server • I’ve got 3 tables: tblOne, tblTwo, tblThree • Using the stored procedure(let call this stored procedure sp_Result) I generate result using all the above tables(tblOne, tblTwo, tblThree). • The problem is, it takes too long to execute(around 15 sec), which is not acceptable as I’m using in the result in the web application • I’m thinking of scheduling to run the above stored procedure to run once every hour and write the result in a new table, tblResult. • now the user only queries in the tblResult tables. Since it is a straight forward query it will take less than 1 sec. Perfect !!! • Now the tblResult already exist, and the user are queries only to this table. • How would I compare the result produced by stored procedure sp_result and the table tblResult? • How would I update tblResult with the produced result? • Would be a good idea to write the result in a new table called temp, then drop the table tblResult and then rename the table temp to tblResult?"
In the process of purging data to history tables, we wanted to make sure that no schema changes have been done to the main or the history table. So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema ( @t1Name NVARCHAR(257) ,@t2Name NVARCHAR(257) ) RETURNS BIT AS /* Compares the schema of 2 tables If the schema is different RETURNS 0 If the schema is identical between the two table, RETURNS 1 NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1) ================================================================================================================== SAMPLE USAGE: DECLARE @schemaOK BIT SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1 PRINT 'TABLE SCHEMA IDENTICAL' ELSE PRINT 'TABLE SCHEMA DIFFERENT' ================================================================================================================== */ BEGIN IF @t1Name = @t2Name RETURN 1
-- check if schema is different IF EXISTS ( SELECT* FROM ( SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE , COLUMN_DEFAULT, IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE , COLLATION_NAME FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1) UNION ALL SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE , COLUMN_DEFAULT, IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE , COLLATION_NAME FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1) ) U GROUP BY COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE HAVING COUNT(*) <> 2 ) RETURN 0
--************************************************************* --SET SQL STMT BASED ON SQL TYPE PASSED IN --*************************************************************/
SELECT Distinct a.station_nbr, c.station_name, a.group_code, a.beg_eff_date As beg_eff_date_DGC, a.end_eff_date As End_eff_date_DGC from TIES_Gathering.dbo.station_group a Inner Join TIES_Gathering.dbo.station c on a.station_nbr = c.station_nbr Inner Join TIES_Gathering.dbo.station_type d on a.station_nbr = d.station_nbr Inner Join TIES_Gathering.dbo.group_detail e on a.group_code = e.group_code Where ((e.group_type = 'NPN')or(e.group_type = 'WTF')or(e.group_type = 'NTF'))and ((d.type_code = 'WHM')or (d.type_code = 'CRP')) and (a.pipeline_code = '100000') Order by a.station_nbr, a.group_code
INSERT INTO #TP_DGC(station_nbr,station_name,group_code,beg_eff_date_DGC,end_eff_date_DGC) values (@stationnumber, @stationame,@groupcode,@begdateG,@endeffdateG)
Select Distinct a.station_nbr,b.station_name,a.group_code, a.beg_eff_date As beg_eff_date_SES, a.end_eff_date As End_eff_date_SES From TIES_Marketing.dbo.station_group a Inner Join TIES_Marketing.dbo.station b on a.station_nbr = b.station_nbr Inner Join TIES_Marketing.dbo.station_type c on a.station_nbr = c.station_nbr Inner Join TIES_Marketing.dbo.group_detail e on a.group_code = e.group_code Where ((c.type_code = 'WHM')or (c.type_code = 'CRP')) and (a.pipeline_code = '100001') Order by a.station_nbr, a.group_code
INSERT INTO #TP_SES(station_nbr,station_name,group_code,beg_eff_date_SES,end_eff_date_SES) values (@stationnumber, @stationame,@groupcode,@begdateM,@endeffdateM)
---------------compare both tables-------------------- Begin
Select #TP_SES.station_nbr, #TP_SES.station_name, #TP_SES.station_group, #TP_DGC.beg_eff_date As beg_eff_date_DGC, #TP_DGC.end_eff_date As End_eff_date_DGC, #TP_SES.beg_eff_date As beg_eff_date_SES,#TP_SES.end_eff_date As End_eff_date_SES from #TP_SES left outer join #TP_DGC on station_nbr=station_nb end