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.
Actually in my transformation i am transferring huge amount of data.
i have been using oledb command finally to dump my incoming data to respective tables.
For Example :
if you have two tables
table 1,table 2
in my incoming data i have a lookup and check for two unique columns with that of the unique columns in the table 1.if the record does not exsist i try inserting a record into table 2 and get the unique filed of the record and store that in particular column of table 1.
the data is very large an is this the better why or any suggesstions do let me know..
Hi All,I've the following table with a PK defined on an IDENTITY column(INSERT_SEQ):CREATE TABLE MYDATA (MID NUMERIC(19,0) NOT NULL,MYVALUE FLOAT NOT NULL,TIMEKEY INTEGER NOT NULL,TIMEKEY_DTTM DATETIME NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) IDENTITY(1,1) NOT NULL)GOALTER TABLE MYDATAADD CONSTRAINT PK_MYDATAPRIMARY KEY (INSERT_SEQ)GOThe TIMEKEY_DTTM field is generated, from the value actually insertedinto theTIMEKEY field, by the following trigger:CREATE TRIGGER TIMEKEY1ON MYDATAFOR INSERT ASBEGINDECLARE @M_TIMEKEY_DTTM DATETIMESELECT @M_TIMEKEY_DTTM = DATEADD(SECOND, INS.TIMEKEY +EP.GMT_OFFSET * 0 ,'1970-01-01 00:00:00.000')FROM INSERTED INS, LOCATIONINFO EPWHERE INS.EID = EP.EIDUPDATE MYDATASET TIMEKEY_DTTM = @M_TIMEKEY_DTTMFROM INSERTED INS, MYDATA MDWHERE MD.INSERT_SEQ = INS.INSERT_SEQENDGOThere is also a composite, non unique, index defined on thetuple:(MID,IID,TIMEKEY,EID)CREATE INDEX IX_METDATA ON MYDATA (MID,IID,TIMEKEY,EID)GOAs a consequence of an application design change, I would also changethis index to be UNIQUE, but when I try to drop and create it I get anerror, because the tables stores some duplicated rows...In order to succesfully upgrade the index definition, I wrote some DMLstaementsto lookup and remove the duplicated rows, keeping only the firstrecord inserted, i.e. the one with the lowest INSERT_SEQ:---- This table stores then umber of duplicated records eventuallydiscovered-- into the MYDATA table; the initial value for the NUM_DUPLICATESfield is-- 0 (no duplicated record)--DROP TABLE DUPLICATESGOCREATE TABLE DUPLICATES (TABLENAME VARCHAR(17),NUM_DUPLICATES NUMERIC(19,0) )GOINSERT INTO DUPLICATES VALUES ('MYDATA',0)GOINSERT INTO DUPLICATES VALUES ('CATEGORIESDATA',0)GO---- ///////// CLEAN UP OF MYDATA TABLE--DROP TABLE TMP_MYDATAGOCREATE TABLE TMP_MYDATA (MID NUMERIC(19,0) NOT NULL,TIMEKEY INTEGER NOT NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) )GO---- Insert into the TMP_MYDATA table all the duplicated records for-- the tuple (MID,IID,TIMEKEY,EID) and NULL for the INSERT_SEQ field--INSERT INTO TMP_MYDATA (MID,IID,TIMEKEY,EID)SELECT MID,IID,TIMEKEY,EIDFROM MYDATAGROUP BY MID,IID,TIMEKEY,EIDHAVING COUNT(*)>1GO---- Updates the INSERT_SEQ field to the lowest value in the group-- of duplicated records--UPDATE TMP_MYDATASET TMP_MYDATA.INSERT_SEQ = (SELECT MIN(INSERT_SEQ)FROM MYDATAWHERE TMP_MYDATA.MID = MYDATA.MID ANDTMP_MYDATA.IID = MYDATA.IID ANDTMP_MYDATA.TIMEKEY = MYDATA.TIMEKEY ANDTMP_MYDATA.EID = MYDATA.EID )GO---- Updates the value of NUM_DUPLICATES for the MYDATA table.--UPDATE DUPLICATESSET NUM_DUPLICATES = (SELECT COUNT(*) FROM TMP_MYDATA)WHERE TABLENAME = 'MYDATA'GO---- Delete from the MYDATA table all the duplicated records,-- keeping only the row with the lowest INSERT_SEQ-- The delete is performed only if there are duplicated recors;-- this is achieved using a "short circuit" AND on the number ofrecords-- stored into the NUM_DUPLICATES field of the DUPLICATES table for-- the MYDATA table...--DELETE FROM MYDATAWHERE ( SELECT NUM_DUPLICATES FROM DUPLICATES WHERE TABLENAME ='MYDATA') > 0 ANDEXISTS ( SELECT 1FROM TMP_MYDATAWHERE MYDATA.MID = TMP_MYDATA.MID ANDMYDATA.IID = TMP_MYDATA.IID ANDMYDATA.TIMEKEY = TMP_MYDATA.TIMEKEY ANDMYDATA.EID = TMP_MYDATA.EID ANDMYDATA.INSERT_SEQ > TMP_MYDATA.INSERT_SEQ )GOThis tecnique works fine on a normal table (1M recs) but is not veryperformanton huge tables (>10M records)!Do you know a better way to achieve the task of removing all theduplicates records, preserving the lowest INSERT_SEQ betwee theduplicates and also preserving the sequence seed, so that a new recordinserted at time t1>t0 is enumerated with an INSERT_SEQ|t1 >max(INSERT_SEQ)|t0 ?Thanks a lot for your help!PatrizioPS. sorry for such a large post!
View 1 Replies View RelatedHi,
I have a situation where I have 4 tables:
1. 2 Dimensional tables(Parent), DIM1 with 50000 rows, and DIM2 with 1000 rows
2. Fact 1 with 50 columns, 25 Million rows and with FK to DIM1 and DIM2
3. Fact 2 with 40 columns, and 25 Million rows and with FK to DIM1 & DIM2 tables.
Actually the fact 1 and fact 2 have same related data but since our Analysis cube person wanted the fact table not to have more than 50 columns we divided the tables into 2, but they have the same compound key.
Above said, I have a situation where I have to select all the columns, in both fact tables, and do a group by. I wrote the query and ran "Analyze Query in the Database Engine Tuning Advisor" for it. It gave bunch of recomendations about the statistics and indexes which I created. When I executed the query the result came up in matter of seconds, which was good.
In the query I had a condition having MarketName='Bridgeview' and DateID = 344 (FK of today-1).
When I wanted the data for last 30 days I changed to DateID in ( > FK of today -32 and < FK of today), the query responded and worked fine.
But when I changed the query to get MarketName='Aurora' (other than I used when I ran Tuning Advisor), the result returned is empty set. When I removed the MarketName condition, it is supposed to return all markets' data, but it returns only Bridgeview data.
I know the data is in the table for all markets, since reports are rendered from these fact tables for all of these markets(also ran queries to check the fact table data).
I am unable to point out the reason why the query behaves like this. It responds to the date change, but not to the MarketName change.
I really appreciate if anyone can help me point out the problem.
Thanks,
Venkat
I have many tables, and I just want to print the relationships between them. Â The ones without foreign keys to primary key relations are irrelevant. Â I made a diagram of all tables in sql server management studio, and it shows the key relations, but its a very large diagram horizontally and vertically. Â Is there a way to print the whole thing so that it doesn't take endless pages that I don't know how to piece together?
View 3 Replies View RelatedTable 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
I am in the process of designing a SQL 2005 database with tables that may hold several hundreds of millions of rows.
Due to various constraints, I am trying to save as much space as possible by optimizing the size of a row. Currently one row contains the following columns:
byte(4), byte(3), byte(3) = 10 bytes.
Adding 4 bytes for the row header, plus 3 bytes for the null bitmap (as described in BOL) I am ending up with 17 bytes/row. In a real world test it was an average of 18.3 bytes/row.
There are no indexes, no primary key and all columns are not NULL. Hence my question: since no column allows a null value, is there a possibility to "remove" the 3 bytes Null Bitmap?
Is there any other way to shave off one or two more bytes by using a clustered index etc?
Thanks
SQL 7 SP1 NT4 SP5
I have a TRANSACTION table with 150 million rows.
I have a USER table.
Each user has about 600 records in the TRANSACTION table.
The TRANSACTION cluster index is on USERID + RECID . The second index is on USERID + Fieldx + Fieldy.
The TRANSACTION table gets about 1.4 million inserts in a normal day and about 40,000 updates.
I want to go through the USER table and delete all users who have not visited me in a while.
I want to do this without substantially hindering performance in a production environment. I can perform this over a week period or two if needed.
The best way I thought of doing this was to grab x amount of users in a cursor and loop through deleting their corresponding TRANSACTION records.
Does anyone have any ideas on a better way. What is going to happen to my indices during this time ?
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?
Thanks,
Bill
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 !!!
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?
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.
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
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.
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
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.
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!
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.
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!
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.
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?
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 & "' )"
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
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 RelatedI 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?
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
-- schema identical
RETURN 1
END
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!!!!!
Hi there,
Can someone please help me figure out how I can write a select statement that returns a row from one table and the count of records from the second table using the row name from the first table?
In other words:
First table has only one column "name" and that table name is called "namelist"
Second table has multiple columns, and the table is called "name"
so, what i want to do is something like:
select name from namelist and select count(*) from [name]
results should be:
sean,0
bob,1
tom,5
etc...
For example, I have 2 tables that have very similar fields
Table 1 Table 2
ShipRpt ShipRpt
invoice invoice
total sold total sold
Is it possible to compare each of these fields and return any record that has non matching data? I would join the tables on the shipping report or invoice and only compare the total sold field. If the invoices match and the total sold is different in each table, can I just return the 1 record?
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
View 3 Replies View Related