473,392 Members | 1,364 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,392 software developers and data experts.

using a stored procedure to compare tables

Hello, Im a novice at this so bare with me.

I have two tables which will be match merged based on 10 conditions
(select statements). For example, if a record exists in table A but
not in Table B then that record will be inserted into a separate audit
table called Table C lets say! So based on this 10 conditions or
select statements that I wrote I would like to create a stored
procedure but im not sure how to begin. Any help would be great. I
don't think that there will be an parameters. Any examples or help
would be greating appreciated Thanks,

WofD
Nov 12 '05 #1
5 4000
You post is very generic...
Of course you can write a procedure.
You may also be able to use regular SQL.
Can't say more without knowing more of tehe details.

Cheers
Serge
Nov 12 '05 #2
Hello Serge,

Okay here is the details. Im using db2 udb v7.1 for windows.

i have 4 tables lets call them, table1 table2 table3 exception4

table1 and table2 have the same columns as follows
REC_TYPE, EMP_NO, HIRE_DATE, DEPART_ID
However, table1 also has the following columns, GROUP_SRC, EMP_GROUP,
EXEMP, INEMP.

There are all chars except hire_date. Also EXEMP and INEMP are either
'Y' or 'N'

Table3 will be inserted with records which match or not-match through
various join statements with multiple where clauses.
i.e insert into table3(t_REC_Type, t_EMP_NO, effect_date, t_depart_id)
select table1.rec_type....blah balh blah
from table1 inner join table2 on table1.emp_no = table2.emp_no
where (now this is problem #1 i need the records to be insert for the
when EXEMP = 'Y' and INEMP = 'N', or when EXEMP = 'N' and INEMP = 'Y',
or when EXEMP = 'N' and INEMP = 'N, or when EXEMP = 'Y' and INEMP =
'Y') so pretty much the different combination of Y and N for the two
fields.

So how would you write such a statement only once instead of 4 times.

Problem #2 how do you write a stored procedure for this. As this
have to be automated on a monthly basis???

Thanks,

WofD
Nov 12 '05 #3
Sorry.. still not enough info.
1. If for INEMP and EXEMP you look at all combinations and always
insert. Why do you look at them at all?
2. You didn't talk at about that exception table
3. Maybe I'm missing something obvious. But if you can write it
interactively what is the issue with the procedure. Is it that the
conditions chaange?

Maybe some example data would be in order?

Cheers
Serge
Nov 12 '05 #4
okay let me try this again. After some more thought i figured this is
would i would like to do. I would like to create a stored procedure
to place data into a temporary table from a right outter join between
TAB1 and TAB2

I would like to create a stored procedure as follows. This is my
pseudo create procedure...

CREATE PROCEDURE INSERT_INTO_TAB3
SPECFIC INSERT_INTO_TAB3
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE
temp_TABLE (empon char(10), col2 char (1)..)
NOT LOGGED ON COMMIT PRESERVE ROWS;

INSERT INTO TEMP_TAB2_SELECT TAB1.EMPON, TAB1.COL2 FROM TAB1 RIGHT
OUTER JOIN TAB2 on TAB1.EMPON = TAB2;
END
=========================================
Now would this work in so far as to populate the temp_table with the
insert statement??? If the above does work then the next part of the
procedure would be to to the query the temporary table for those
records in which col2 = 'Y' and insert those records into another
table called TAB3 and if col2 = 'N' then insert into another table
called TAB4. Any ideas???

Thanks
Nov 12 '05 #5
Yes, absolutely. That will work.

Cheers
Serge
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Aaron | last post by:
The following code works fine when previewing a Crystal report using ASP, EXCEPT when it gets to a report using a SubReport and its associated parameters. The whole report just comes up blank with...
4
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if...
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
2
by: Roy | last post by:
Hey all, Here's a small VB codeblock that connects to a database and uses 2 SQL queries then forms a relation for a master/detail view on the aspx side: Private Sub Binddata(ByVal name As...
2
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: ...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
1
by: tezza98 | last post by:
I need some help. Im using a dtsrun command to import 9 tables into an Access database, most of the tables have about 1000 rows, but one has 20000+ rows and grows everyday. Im Using Access 2003...
2
by: acw | last post by:
On a SQL Server 2000 db I would like to setup a stored procedure that accesses couple tables and runs the extended stored procedure xp..cmdshell. The goal is to grant users with limited privileges...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.