October 11, 2007 at 9:54 am
1.What is the best way to insert data into a table from multiple tables and how?
2. How can I run a sheduled job so that it inserts only new data generated in the source tables, ignoring the data which was already imported in the prevous INSERT.
October 11, 2007 at 10:42 am
1. well - with the question wide open like that - there are several options. The most general way to do that is through a series of INSERT statements.
Insert <runningtable> (<fields>) select <fields> from A
Insert <runningtable> (<fields>) select <fields> from B
etc....
If the fieldnames you're inserting is the SAME across all tables, you could try a slight variant of above:
Insert <runningtable> (<fields>)
select <fields> from A
UNION ALL
select <fields> from B
UNION ALL
select <fields> from C
etc...
However - if these tables are each "big" - the overhead of the UNION's is usually more than the overhead of multiple INSERT operations. So - I'd stick with option #1.
If you litterally want everything from every table, then the order of the inserts doesn't matter.
2. In order to not allow "dupes", you'd need some extra rules defining what CONSTITUTES a duplicate in this specific case. Meaning - what fields need to match in order for the row to be considered a duplicate? Is it a duplicate if it matches to a row imported from ANOTHER table (that would require changing what happens on the INSERT)?
Example:
Table A-F and runningTable all have 30 fields about locations (say - addresses). We want to treat an address as a duplicate if Address1,Address2 and zip all match. There's an addressID field that is always populated on every record in runningTable.
Ongoing inserts from A into runningTable would look like:
Insert into runningtable (address1,address2, zip, ec....)
select a.address1, a.address2, a.zip, a. .....
from A left outer join runningtable R
on a.address1=r.address1 and a.address2=r.address2 and a.zip=r.zip
Where r.addressid is null ----this means we didn't find a match.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 12, 2007 at 9:51 am
If you have date column that audits the insert time, then you can use this column to validate the records that are inserted after your previous job execution time and insert only those rows by selecting rows where datecol > .
Prasad Bhogadi
www.inforaise.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply