Hello All,
I am trying to create a DTS package.
I have two tables tbl_A and tbl_B with similar data/rows but no
primary keys.
tbl_A is master.
I would like this package to query tbl_A and tbl_B and find
1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_A
that are not present in tbl_B and
3)all rows in tbl_B that are not present in tbl_A, and then just show
those rows.
Can this be done with a simple UNION?
Perhaps this could produce a temp Table that can be dropped once the
DTS package exists successfully.
The 2nd part after all the above rows are retrieved is that I would
like to add an addional Column to the retrieved data called STATUS
which has 3 possible values(letters) at the end of each row...
M (modified) means that row exists in tbl_B but has 1 or more
different columns
A (add) means this row exists in tbl_A but not in tbl_B
D (delete) means this row exists in tbl_B but not in tbl_A
I'm hopping this DTS package would output a nice comma seperated TXT
file with only...
1) rows from tbl_A that are different in tbl_B (STATUS M)
2) rows from tbl_A that are not present in tbl_B (STATUS A)
3) rows from tbl_B that are not present in tbl_A (STATUS D)
Can a DTS package in MS SQL be used to perfom all of the above tasks?
I would very much appreciate any help or any advise.
Thanks in advance :-) 7 4552
jb1 wrote:
I am trying to create a DTS package.
I have two tables tbl_A and tbl_B with similar data/rows but no
primary keys.
tbl_A is master.
I would like this package to query tbl_A and tbl_B and find
1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_A
that are not present in tbl_B and
3)all rows in tbl_B that are not present in tbl_A, and then just show
those rows.
In the absence of primary keys, what conceptual rule do you use to
associate rows in tbl_A with rows in tbl_B? For instance, consider
this hypothetical data:
[tbl_A]
col1 | col2
-----+-----
1 | 2
3 | 4
[tbl_B]
col1 | col2
-----+-----
1 | 4
3 | 2
What associations would you draw here, and why?
Can this be done with a simple UNION?
Perhaps this could produce a temp Table that can be dropped once the
DTS package exists successfully.
Why not a view?
The answer is to write 3 queries, 1 for each situation you describe
and link the output using the UNION ALL statement
Something like
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
b on a.id = b.id
WHERE a.col1 <b.col1
UNION ALL
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a LEFT JOIN
table2 b on a.id = b.id
WHERE b.col4 IS NULL
UNION ALL
SELECT b.id, b.col1, b.col2, b.col3, a.col4 FROM table2 b LEFT JOIN
table1 a on b.id = a.id
WHERE a.col4 IS NULL
Note that the col1, col2, col3 and col4 in each of the queries must be
of the same datatypes or can be converted to the same datatypes e.g.
VARCHAR(20) is the same as CHAR(5). The where clauses can be more
specific and varied than I have shown but the important part is the
test for IS NULL in the last 2 queries as this is the way to find rows
in one table and that are not in another.
Hope this helps
On 12 Jun, 16:24, undercups <d...@woodace.c o.ukwrote:
The answer is to write 3 queries, 1 for each situation you describe
and link the output using the UNION ALL statement
Something like
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
b on a.id = b.id
WHERE a.col1 <b.col1
UNION ALL
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a LEFT JOIN
table2 b on a.id = b.id
WHERE b.col4 IS NULL
UNION ALL
SELECT b.id, b.col1, b.col2, b.col3, a.col4 FROM table2 b LEFT JOIN
table1 a on b.id = a.id
WHERE a.col4 IS NULL
That's great! Thank you. I'll have a look into this.
Do you have any ideas how I can add the above mentioned STATUS column
after each returned row? eg. If row is different in tbl_B from tbl_A
then add 'M' at the end of the row, as has Master data. something
like.. ("col1.value"," col2.value","co l3.value","M")
Thanks again :-)
Note that the col1, col2, col3 and col4 in each of the queries must be
of the same datatypes or can be converted to the same datatypes e.g.
VARCHAR(20) is the same as CHAR(5). The where clauses can be more
specific and varied than I have shown but the important part is the
test for IS NULL in the last 2 queries as this is the way to find rows
in one table and that are not in another.
Hope this helps
jb1 (ja***********@ googlemail.com) writes:
On 12 Jun, 16:24, undercups <d...@woodace.c o.ukwrote:
>The answer is to write 3 queries, 1 for each situation you describe and link the output using the UNION ALL statement
Something like
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2 b on a.id = b.id WHERE a.col1 <b.col1
Note here that you should probably compare all columns. If columns can
be nullable, this can be quite hairy.
That's great! Thank you. I'll have a look into this.
Do you have any ideas how I can add the above mentioned STATUS column
after each returned row? eg. If row is different in tbl_B from tbl_A
then add 'M' at the end of the row, as has Master data. something
like.. ("col1.value"," col2.value","co l3.value","M")
Just att the desired status letter to the column list of the queries,
for instance:
SELECT a.id, a.col1, a.col2, a.col3, b.col4, 'M' AS status
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
undercups wrote:
The answer is to write 3 queries, 1 for each situation you describe
and link the output using the UNION ALL statement
Something like
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
b on a.id = b.id
WHERE a.col1 <b.col1
UNION ALL
(etc.)
The problem is that, if the tables don't have primary keys, then they
may not have a column like 'id'. (Or maybe they do, and it just isn't
defined as a primary key - in which case, why not?)
On 13 Jun, 06:25, Ed Murphy <emurph...@soca l.rr.comwrote:
undercups wrote:
The answer is to write 3 queries, 1 for each situation you describe
and link the output using the UNION ALL statement
Something like
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
b on a.id = b.id
WHERE a.col1 <b.col1
UNION ALL
(etc.)
The problem is that, if the tables don't have primary keys, then they
may not have a column like 'id'. (Or maybe they do, and it just isn't
defined as a primary key - in which case, why not?)
Well there are about 5 or 6 cols in each table and I need to check
each one to see if anything has changed. There is 1 column in each
that is kind of like the ID you are talking about though it is not set
to Primary Key.
Anyway, this is what I have for the 1st query to find Modified rows in
TableMaster...
SELECT a.id,
a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'M' AS status
FROM TableMaster a JOIN TableSlave b on a.id = b.id
WHERE (a.col2 <b.col2) OR (a.col3 <b.col3) OR (a.col4 <b.col4)
OR (a.col5 <b.col5)
UNION ALL
Now does the UNION ALL command at the end imply that another query
will follow and that the results from this query and the other query
be joined?
Will the above view query return what I'm looking for? - rows in
TableMaster that are different in TableSlave? so tha later I can
update TableSlave with these new modified row.
How could I continue and query Rows that are present in TableMaster
but not in TableSlave? - Status 'A' (add)
Finally, I would like to put a 3rd query in for TableMaster and
TableGrandMaste r, which tells me which rows are present in
TableGrandMaste r but missing in TableMaster - Status 'D' (delete)
Thank you again everyone for all your help and advise! :-)
JB
jb1 wrote:
On 13 Jun, 06:25, Ed Murphy <emurph...@soca l.rr.comwrote:
>undercups wrote:
>>The answer is to write 3 queries, 1 for each situation you describe and link the output using the UNION ALL statement Something like SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2 b on a.id = b.id WHERE a.col1 <b.col1 UNION ALL
(etc.)
The problem is that, if the tables don't have primary keys, then they may not have a column like 'id'. (Or maybe they do, and it just isn't defined as a primary key - in which case, why not?)
Well there are about 5 or 6 cols in each table and I need to check
each one to see if anything has changed. There is 1 column in each
that is kind of like the ID you are talking about though it is not set
to Primary Key.
Like I said, why isn't it? Is there a good reason not to set it
to Primary Key now?
Anyway, this is what I have for the 1st query to find Modified rows in
TableMaster...
SELECT a.id,
a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'M' AS status
FROM TableMaster a JOIN TableSlave b on a.id = b.id
WHERE (a.col2 <b.col2) OR (a.col3 <b.col3) OR (a.col4 <b.col4)
OR (a.col5 <b.col5)
UNION ALL
Now does the UNION ALL command at the end imply that another query
will follow and that the results from this query and the other query
be joined?
Yes. ALL means to include duplicates, which is unnecessary in this
case (the first section of the query will contribute rows with
status = M, the second will contribute rows with status = A, etc.).
Will the above view query return what I'm looking for? - rows in
TableMaster that are different in TableSlave? so tha later I can
update TableSlave with these new modified row.
Yes, assuming that 'id' values are not changed or duplicated.
How could I continue and query Rows that are present in TableMaster
but not in TableSlave? - Status 'A' (add)
For parallelism with the part before UNION ALL:
select a.id, a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'A' AS status
from TableMaster a
left join TableSlave b on a.id = b.id
where b.id is null
Finally, I would like to put a 3rd query in for TableMaster and
TableGrandMaste r, which tells me which rows are present in
TableGrandMaste r but missing in TableMaster - Status 'D' (delete)
select a.id, a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'D' AS status
from TableSlave b
left join TableMaster a on a.id = b.id
where a.id is null
There's also RIGHT JOIN, but I avoid it because it's confusing, and
one of the main packages I work on doesn't allow it anyway. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: udo polder |
last post by:
hello
can anyone tell me if it is possible to use activeX components (com/dcom) in
MS-sql stored procedures?
tia
|
by: chris.dunigan |
last post by:
I'm looking for an example of how to execute an existing DTS package
from an
ASP (VB)script and would appreciate any and all response. I don't
even
know if it's possible
Thanks
- Chuck Gatto
Dan Guzman Apr 27 2000, 12:00 am show options
|
by: smonczka |
last post by:
I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.
I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row...
|
by: Fred Zuckerman |
last post by:
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
Thanks,
Fred Zuckerman
|
by: Bob Stearns |
last post by:
I am getting duplicate rows back from a select distinct statement of the
form:
SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT lots of good stuff) t0
LEFT OUTER JOIN another_table t1 ON relevant_stuff
WHERE (lots of conditions)
After re-reading the relevant pat ofVol 1 of the SQL Reference I am
unablee to see how this is possible.
| |
by: james.benson1 |
last post by:
Hello All,
I am trying to create a DTS package.
I have two tables tbl_A and tbl_B with similar data/rows but no
primary keys.
tbl_A is master.
I would like this package to query tbl_A and tbl_B and find
1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_A
that are not present in tbl_B and
|
by: guswebb |
last post by:
Hi. I'm a newbie to PHP and am having a few problems as follows...
I have installed PHP successfully on server 1 which is running IIS 6 (W2k3) and hosting multiple sites, some of which connect to MSSQL 2k (SP4) on server 2 (using ASP).
I can load a basic 'Hellow world' PHP page hosted on server1 but when I add the code to create a simple connection to MSSQL on server 2, my PHP doesn't seem to connect nor output any of the desired query...
|
by: ukchat |
last post by:
I.m creating a dynamic query to pull out workbooks from my database the table structure is below.
Table: curricworkbooks
Columns: ID, curric, assessment, topic, workbook, filename
Example data
1 N1/E1.1 Numeracy E1 Count 1 workbooks/Num Entry 1/Unit 1/04 N1E1.1-3 Worksheets Num.pdf
10 MSS1/E1.6 Numeracy E1 Capacity 13 workbooks/Num Entry 1/Unit 13/04 MSS1E1.6 Worksheets Num.pdf
|
by: omeek |
last post by:
I'm new to MSSQL (Have used MySQL for a little while) and am completely stuck. I have searched the net for days now and am amazed at how little info is out there for MSSQL.
I am selecting multiple rows from one DB and need to insert those rows in to a table in a different DB and I can not figure out how to do it. Im using PHP. I have not been able to find a function for MSSQL similar to mysql_insert_array.
The SELECT output is creating a...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |