DTS Package - Compare Row Counts Between Tables

Jun 14, 2005

Hello,
I am working on my first DTS package in about 2 years. I am using MS SQL Server 2000.

I have 10 tables and will be importing those tables from 10 text files each night. The files will be imported to tables with temp_ as a prefix. What I need to do is then compare the row counts of the imported (temp_) tables to the ones that are there from the previous import (tables without the temp_ prefix). If the row count between each of the tables is greater than a 10% variance +/- then I need to abort the import and send an email to the client. If the variance is less than 10% for all 10 tables then I need to conitinue with the rest of the DTS which will drop all the tables from the previous import and then rename all the imported tables to remove the temp_ prefix so that they become the new "production" tables. I have it setup to import to the temp_ tables and that is working just fine. I have it setup to drop and rename the tables... what I need help with is how I would go about doing the row count comparison. Can in be done directly in DTS, do I need to do it in a stored procedure called by the DTS? If someone can just get me pointed in the right direction with this it would be great!

Thanks,
Jake

View 2 Replies


ADVERTISEMENT

Compare Row Counts

Oct 6, 2006

Hi all,

I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.

So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End

I need to put the above logic in a sp that I want to execute using a job.

Help is appreciated.

V

View 5 Replies View Related

Compare Counts [SOLVED]

Mar 12, 2008

Hi, hope someone can help. I have two tables

Test1 that lists all the training courses that i can count to find out the total as below.

select count (distinct Training_Course) as total
from Test1

Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id

What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.

Make any sense? Any help/suggestions gratefully recieved.

View 4 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

List Record Counts Of All Tables?

Jun 2, 2004

Hi All,

Is there a fancy way to list all table names with record counts?
Using table: INFORMATION_SCHEMA.TABLES

Also, Is there a way to initialize/empty all data from all tables?

Thank you very much

View 2 Replies View Related

Tables And Rows Counts PIVOT

May 26, 2015

Script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g

Database || TableName1| Tablename2|| RowsCounts

View 2 Replies View Related

Rename Tables With 0 Record Counts

Apr 1, 2008

I want to write a proc that will loop through a database and if a table has 0 record counts then the table is renamed to 'ZZZ_tablename'. Should I use this to get the row counts? or what is the best way to do this?

DBCC UPDATEUSAGE(GCMC_DISCGRX) WITH COUNT_ROWS

select object_name(id) as table_name,rows from sysindexes
where indid<2 AND INDID = 0 and rows = 0
order by 1

then EXEC sp_rename @emptytables, 'ZZZ_' + @emptytables

View 2 Replies View Related

SQL 2012 :: Passing Record Counts From Execute Task In SSIS To Package Variables

Mar 4, 2014

I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.

The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.

I've tried returning the values as:

Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'

I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).

View 2 Replies View Related

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

Jan 15, 2015

I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now

Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1

Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.

Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1

I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.

View 9 Replies View Related

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)

Jul 22, 2007

Table MediaImportLog
column ↘ImportIndex     ImportFileTime            ImportSource
value    ↘80507             20060506001100          815
              80511             20061109120011           CRD                       ã€? P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table  BillerChain
column↘BillerInfoCode       ChainCode
value   ↘750                      815
value   ↘81162                  CRD
Table   Biller
column↘CompanyCode         BillerCode
value   ↘999                     750
value   ↘81162                  516
TAble DataBackup
column↘CompanyCode         Keepmonth
value   ↘999                     6
value   ↘81162                 12
 
---------------------------------------------------
 
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 ? 
 
thank you very much 

View 3 Replies View Related

Compare Two Tables

Dec 8, 2000

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.

View 1 Replies View Related

Can I Compare Two Tables

Jan 14, 2000

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?

Thanks,
Bill

View 2 Replies View Related

How To Compare 2 Tables ??

Jul 1, 2004

Hi,

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 






PLEASE HELP !! Thanks in advance !!!

View 3 Replies View Related

How To Compare Each Row In Two Tables

Jul 10, 2012

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.

How to create this script?

View 3 Replies View Related

Compare 2 Tables

Jun 8, 2006

I have 2 tables that are exactally the same.

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.

Thanks in advance for any help/suggestions.

View 2 Replies View Related

How To Compare 2 Tables

Dec 17, 2007

need to know how to check both table data and return only data that doesn't match the other table data


select distinct dept_name
from bb_guide_party_dept d, binbox_dept e where d.dept_id = e.dept_id
and e.active_Flag = 1

union
select distinct dept_name
from bb_guide_party_dept a, binbox_dept b where a.dept_id = b.dept_id
and e.dept_name not the same as b.dept_name

View 3 Replies View Related

How To Compare Different Tables

Jan 31, 2008

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.

View 1 Replies View Related

How To Compare Data In Tables

Sep 13, 2001

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!

rb

View 2 Replies View Related

Compare Two Tables...URGENT!

Dec 7, 2000

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.

View 1 Replies View Related

Compare/filter 2 Tables...help!!!

May 13, 2004

Would appreciate if someone could 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.

Thanks for your help!

View 3 Replies View Related

Query To Compare Two Tables

Aug 4, 1999

Hi,

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.

View 1 Replies View Related

How Do You Compare The Data In Two Tables

Sep 29, 2005

I have two tables and I want to know if every record from the first table is in the second one and if its data mathes exactly?

Any suggestion for a short way to do this?

Thank you!

View 10 Replies View Related

Compare Each Rows In Two Tables?

Nov 16, 2011

I need to compare two tables in each row. the tables are as follows:-

Table a:

Code:
IDFirst_Name Last_name Birthdate
1Shradha Deshkmukh 1981-12-25 00:00:00
2Shradha Verma 1981-05-11 00:00:00
3Divya Dutta 1982-07-21 00:00:00
4Matthew Palmer 1983-12-28 00:00:00

table d:-

Code:
idfnlndob
1ShradhaTiwari1981-12-25 00:00:00
2DivyaDutta1983-07-21 00:00:00
3SulabhManesar1975-09-11 00:00:00
4MatthewPalmer1983-12-28 00:00:00
5SamuelMaxwell1984-05-22 00:00:00

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

[code]...

the result I am getting is :-

Code:
fnlndob (No column name)
ShradhaTiwari1981-12-25 00:00:00match
ShradhaTiwari1981-12-25 00:00:00match
DivyaDutta1983-07-21 00:00:00match
MatthewPalmer1983-12-28 00:00:00match

I have tried using CASE but that doesnt work either.

View 3 Replies View Related

How To Compare Two Huge Tables

Aug 15, 2012

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.

View 6 Replies View Related

Compare Amounts Between 2 Tables

Sep 21, 2012

I have two tables. One is Invoice_tbl, with one account per customer.

This table has 3 fields; CustomerID, InvoiceAmount, InvoiceID.

Then second table is, Payment_tbl, with 2 fields; InvoiceID and PaymentAmount. The Payment table can have multiple payments from each customer.

With Access, i would run a QUERY(call it PaymentTotal) against Payment_tbl, then do a "GroupBy" on InvoiceID and SUM on the "Amount" field.

I then would create a NEW query against Invoice_tbl and INNER JOIN on Payment Total.

How would i do this with SQL?

View 6 Replies View Related

Compare Update On Two Tables?

Feb 27, 2014

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 & "' )"

View 7 Replies View Related

How To Compare 3 Columns From 2 Tables

Jul 26, 2013

I have requirement like this

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

View 5 Replies View Related

Query To Compare Two Tables

Dec 26, 2013

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

View 2 Replies View Related

Compare Tables Of Different Servers

Oct 8, 2014

I have two databases under two different servers.

Server1:
DB1
Table1

Server2:
DB2
Table2

I need to compare Table 1 & 2 using SQL server and how can I achieve this?

View 2 Replies View Related

Query To Compare Two Tables

Nov 4, 2005

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?

View 1 Replies View Related

Compare Two Tables And Update Or ????

Aug 7, 2006

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?"

View 6 Replies View Related

Compare Schema Between Tables

Oct 6, 2006

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

-- schema identical
RETURN 1
END

View 6 Replies View Related

I Need Help On How To Compare Two TEMP Tables?

Nov 2, 2007

I need help on how to compare two temp tables. I have to compare two temp tables from two diferent server. Here is what I have done so far...

CREATE PROC dbo.swn_sp

AS
/********************************************************************
*Program Name:
*
*
*********************************************************************
*
*Modification History:
*********************************************************************

--*************************************************************
--SET SQL STMT BASED ON SQL TYPE PASSED IN
--*************************************************************/

/* Create a Temp Table */

Create Table #TP_DGC(
station_nbr char (20),
station_name varchar (40),
group_code char (12),
beg_eff_date_DGC datetime,
end_eff_date_DGC datetime)


--------------Selection Gathering Table-------------------


DECLARE @groupcodechar (12),
@stationnumber char(20),
@stationamevarchar(40),
@begdateGdatetime,
@endeffdateGdatetime,
@Recordsint,
@record_found_flagchar(1),
@current_date datetime


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)


--------------Selection Marketing Table-------------------


Create Table #TP_SES(
station_nbr char (20),
station_name varchar (40),
group_code char (12),
beg_eff_date_SES datetime,
end_eff_date_SES datetime)



DECLARE @groupcode_Mchar (12),
@stationnumber_M char(20),
@stationame_Mvarchar(40),
@begdateMdatetime,
@endeffdateM datetime,
@Records_Mint,
@record_found_flag_Mchar(1)


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


----- drop temporary tables---

drop table #TP_DGC
drop table #TP_SES

go



Thanks in advance for the Help!!!!!

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved