Need to compare data in SQL to Oracle / import changes

We will need to routinely import only changed data from an Oracle data base into a SQL database.
So we need an agent that will
1) Compare data in both databases (From disparate tables)
2) Import only that which is changes or new.

I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis.
I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution.
Can someone point me in the right direction?
Thanks.How much data are we talking about?

Check out this

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx

need ideas on how to approach this issue

----
--
All,
Need ideas on how to approach this I'm writing a c# program:
Have to compare account numbers in one table to account numbers in
another table and if they exist in the second table
The problem is the tables are in two separate databases (two different
physical places). One table in the ERP system and the other is in the
financial system.
table1 ERP system
table2 Financial system
1. Should I dump table 1 into dataset table, do the same with table
two. This way they are both in the same dataset then figure out how to
compare 1 to 2?
2. Do I dump table 1 into an array and iterate through the array
making sql calls (using a data reader) to see if the account numbers
exist in second table?
3. Put both tables in the arrays and hack away?
What do you consider a good approach or best practice?
Thanks in advance
RCThe fast way is to somehow get all the numbers in one location (on one
server). You can then execute a SQL statement that performs a JOIN or uses
WHERE EXISTS to determine which numbers match.
You could use DTS to get the data out. Or you could read them to a file
(from the one server) and then build insert statements to load the data into
a temp table (denoted by a # sign) on the other server -- against which you
would run the appropriate query.
Keith
"RC" <rick_castrejon@.email.com> wrote in message
news:1120869189.371998.244770@.g14g2000cwa.googlegroups.com...
> ----
--
> All,
> Need ideas on how to approach this I'm writing a c# program:
> Have to compare account numbers in one table to account numbers in
> another table and if they exist in the second table
> The problem is the tables are in two separate databases (two different
> physical places). One table in the ERP system and the other is in the
> financial system.
> table1 ERP system
> table2 Financial system
> 1. Should I dump table 1 into dataset table, do the same with table
> two. This way they are both in the same dataset then figure out how to
> compare 1 to 2?
> 2. Do I dump table 1 into an array and iterate through the array
> making sql calls (using a data reader) to see if the account numbers
> exist in second table?
> 3. Put both tables in the arrays and hack away?
> What do you consider a good approach or best practice?
> Thanks in advance
> RC
>

need ideas on how to approach this issue

All,
Need ideas on how to approach this I'm writing a c# program:
Have to compare account numbers in one table to account numbers in
another table and if they exist in the second table
The problem is the tables are in two separate databases (two different
physical places). One table in the ERP system and the other is in the
financial system.
table1 ERP system
table2 Financial system
1. Should I dump table 1 into dataset table, do the same with table
two. This way they are both in the same dataset then figure out how to
compare 1 to 2?
2. Do I dump table 1 into an array and iterate through the array
making sql calls (using a data reader) to see if the account numbers
exist in second table?
3. Put both tables in the arrays and hack away?
What do you consider a good approach or best practice?
Thanks in advance
RC
The fast way is to somehow get all the numbers in one location (on one
server). You can then execute a SQL statement that performs a JOIN or uses
WHERE EXISTS to determine which numbers match.
You could use DTS to get the data out. Or you could read them to a file
(from the one server) and then build insert statements to load the data into
a temp table (denoted by a # sign) on the other server -- against which you
would run the appropriate query.
Keith
"RC" <rick_castrejon@.email.com> wrote in message
news:1120869189.371998.244770@.g14g2000cwa.googlegr oups.com...
> ----
> All,
> Need ideas on how to approach this I'm writing a c# program:
> Have to compare account numbers in one table to account numbers in
> another table and if they exist in the second table
> The problem is the tables are in two separate databases (two different
> physical places). One table in the ERP system and the other is in the
> financial system.
> table1 ERP system
> table2 Financial system
> 1. Should I dump table 1 into dataset table, do the same with table
> two. This way they are both in the same dataset then figure out how to
> compare 1 to 2?
> 2. Do I dump table 1 into an array and iterate through the array
> making sql calls (using a data reader) to see if the account numbers
> exist in second table?
> 3. Put both tables in the arrays and hack away?
> What do you consider a good approach or best practice?
> Thanks in advance
> RC
>

need ideas on how to approach this issue

----
All,
Need ideas on how to approach this I'm writing a c# program:
Have to compare account numbers in one table to account numbers in
another table and if they exist in the second table
The problem is the tables are in two separate databases (two different
physical places). One table in the ERP system and the other is in the
financial system.
table1 ERP system
table2 Financial system
1. Should I dump table 1 into dataset table, do the same with table
two. This way they are both in the same dataset then figure out how to
compare 1 to 2?
2. Do I dump table 1 into an array and iterate through the array
making sql calls (using a data reader) to see if the account numbers
exist in second table?
3. Put both tables in the arrays and hack away?
What do you consider a good approach or best practice?
Thanks in advance
RCThe fast way is to somehow get all the numbers in one location (on one
server). You can then execute a SQL statement that performs a JOIN or uses
WHERE EXISTS to determine which numbers match.
You could use DTS to get the data out. Or you could read them to a file
(from the one server) and then build insert statements to load the data into
a temp table (denoted by a # sign) on the other server -- against which you
would run the appropriate query.
--
Keith
"RC" <rick_castrejon@.email.com> wrote in message
news:1120869189.371998.244770@.g14g2000cwa.googlegroups.com...
> ----
> All,
> Need ideas on how to approach this I'm writing a c# program:
> Have to compare account numbers in one table to account numbers in
> another table and if they exist in the second table
> The problem is the tables are in two separate databases (two different
> physical places). One table in the ERP system and the other is in the
> financial system.
> table1 ERP system
> table2 Financial system
> 1. Should I dump table 1 into dataset table, do the same with table
> two. This way they are both in the same dataset then figure out how to
> compare 1 to 2?
> 2. Do I dump table 1 into an array and iterate through the array
> making sql calls (using a data reader) to see if the account numbers
> exist in second table?
> 3. Put both tables in the arrays and hack away?
> What do you consider a good approach or best practice?
> Thanks in advance
> RC
>

Need help writing query

I have a table with items bought by the sec. I would like to group them by 5
min intervals round the clock for a day and compare them with the same value
for other days of the w around the same time to get a percentage trend.
So input in a table A for eg: would be
TableA
Date1 Count1
2/1/2006 00:01 1
2/1/2006 00:03 1
2/1/2006 00:05 1
2/1/2006 00:07 1
2/1/2006 00:09 1
2/1/2006 00:11 1
2/1/2006 00:16 1
2/1/2006 01:03 1
2/1/2006 01:05 1
2/1/2006 01:13 1
2/2/2006 00:01 1
2/2/2006 00:03 1
2/2/2006 00:05 1
2/2/2006 00:07 1
2/2/2006 00:08 1
2/2/2006 00:09 1
2/2/2006 00:11 1
2/2/2006 00:16 1
2/2/2006 01:03 1
2/2/2006 01:05 1
2/2/2006 01:13 1
Create table tableA
(Date1 datetime,
count1 int)
insert tableA values('2/1/2006 00:01 ' , 1)
insert tableA values('2/1/2006 00:03' , 1)
insert tableA values('2/1/2006 00:05' , 1)
insert tableA values('2/1/2006 00:07' , 1)
insert tableA values('2/1/2006 00:09' , 1)
insert tableA values('2/1/2006 00:11' , 1)
insert tableA values('2/1/2006 00:16' , 1)
insert tableA values('2/1/2006 01:03' , 1)
insert tableA values('2/1/2006 01:05' , 1)
insert tableA values('2/1/2006 01:13' , 1)
insert tableA values('2/2/2006 00:01' , 1)
insert tableA values('2/2/2006 00:03' , 1)
insert tableA values('2/2/2006 00:05' , 1)
insert tableA values('2/2/2006 00:07' , 1)
insert tableA values('2/2/2006 00:08' , 1)
insert tableA values('2/2/2006 00:09' , 1)
insert tableA values('2/2/2006 00:11' , 1)
insert tableA values('2/2/2006 00:16' , 1)
insert tableA values('2/2/2006 01:03' , 1)
insert tableA values('2/2/2006 01:05' , 1)
insert tableA values('2/2/2006 01:13' , 1)
Output required
Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
00 0 0 0
00 5 2 2
00 10 3 4
00 15 1 1
00 20 1 1
00 25 0 0
00 30 0 0
00 35 0 0
00 40 0 0
00 45 0 0
00 50 0 0
00 55 0 0
01 00 0 0
01 5 2 2
01 10 3 3
01 15 1 1
01 20 1 1
01 25 0 0
01 30 0 0
01 35 0 0
01 40 0 0
01 45 0 0
01 50 0 0
01 55 0 0
........
As you can see, I would like to group within 5 minute intervals of the hour.
I would then like to pivot the dates so I can trend day over day. Ideally Id
like to group daily for 7 days .. that way its not dynamic
Can someone assist ?Hassan
Take a look at Erland's example. Perhaps it is not exactly what you wanted
but it certainly give you an idea
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 5
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eSVEB7wOGHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a table with items bought by the sec. I would like to group them by
>5 min intervals round the clock for a day and compare them with the same
>value for other days of the w around the same time to get a percentage
>trend.
> So input in a table A for eg: would be
> TableA
> Date1 Count1
> 2/1/2006 00:01 1
> 2/1/2006 00:03 1
> 2/1/2006 00:05 1
> 2/1/2006 00:07 1
> 2/1/2006 00:09 1
> 2/1/2006 00:11 1
> 2/1/2006 00:16 1
> 2/1/2006 01:03 1
> 2/1/2006 01:05 1
> 2/1/2006 01:13 1
> 2/2/2006 00:01 1
> 2/2/2006 00:03 1
> 2/2/2006 00:05 1
> 2/2/2006 00:07 1
> 2/2/2006 00:08 1
> 2/2/2006 00:09 1
> 2/2/2006 00:11 1
> 2/2/2006 00:16 1
> 2/2/2006 01:03 1
> 2/2/2006 01:05 1
> 2/2/2006 01:13 1
>
> Create table tableA
> (Date1 datetime,
> count1 int)
> insert tableA values('2/1/2006 00:01 ' , 1)
> insert tableA values('2/1/2006 00:03' , 1)
> insert tableA values('2/1/2006 00:05' , 1)
> insert tableA values('2/1/2006 00:07' , 1)
> insert tableA values('2/1/2006 00:09' , 1)
> insert tableA values('2/1/2006 00:11' , 1)
> insert tableA values('2/1/2006 00:16' , 1)
> insert tableA values('2/1/2006 01:03' , 1)
> insert tableA values('2/1/2006 01:05' , 1)
> insert tableA values('2/1/2006 01:13' , 1)
> insert tableA values('2/2/2006 00:01' , 1)
> insert tableA values('2/2/2006 00:03' , 1)
> insert tableA values('2/2/2006 00:05' , 1)
> insert tableA values('2/2/2006 00:07' , 1)
> insert tableA values('2/2/2006 00:08' , 1)
> insert tableA values('2/2/2006 00:09' , 1)
> insert tableA values('2/2/2006 00:11' , 1)
> insert tableA values('2/2/2006 00:16' , 1)
> insert tableA values('2/2/2006 01:03' , 1)
> insert tableA values('2/2/2006 01:05' , 1)
> insert tableA values('2/2/2006 01:13' , 1)
> Output required
> Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
> 00 0 0 0
> 00 5 2 2
> 00 10 3 4
> 00 15 1 1
> 00 20 1 1
> 00 25 0 0
> 00 30 0 0
> 00 35 0 0
> 00 40 0 0
> 00 45 0 0
> 00 50 0 0
> 00 55 0 0
> 01 00 0 0
> 01 5 2 2
> 01 10 3 3
> 01 15 1 1
> 01 20 1 1
> 01 25 0 0
> 01 30 0 0
> 01 35 0 0
> 01 40 0 0
> 01 45 0 0
> 01 50 0 0
> 01 55 0 0
> ........
>
> As you can see, I would like to group within 5 minute intervals of the
> hour. I would then like to pivot the dates so I can trend day over day.
> Ideally Id like to group daily for 7 days .. that way its not dynamic
> Can someone assist ?
>
>|||On Sun, 26 Feb 2006 11:56:36 -0800, Hassan wrote:

>I have a table with items bought by the sec. I would like to group them by
5
>min intervals round the clock for a day and compare them with the same valu
e
>for other days of the w around the same time to get a percentage trend.
(snip)
Hi Hassan,
Thanks for posting CREATE TABLE and INSERT statements and expected
output. This made it very easy to develop the query below, which will
return the expected results, BUT:
1. Rows with only 0 count are excluded. If you really need them, you'll
have to add a numbers table to the query to get the desired result (let
me know if you need assistance with that part as well)
2. Not all output matches your expected output. I think that the errors
are in your post. If not, I must have misunderstood the requirements.
Anyway, here's the query:
DECLARE @.BaseDate datetime
SET @.BaseDate = '20060201'
SELECT FiveMinIntervals / 12 AS Hours,
FiveMinIntervals % 12 * 5 AS Minutes,
SUM(CASE WHEN Days = 0 THEN count1 ELSE 0 END) AS Day1,
SUM(CASE WHEN Days = 1 THEN count1 ELSE 0 END) AS Day2,
-- repeat some more times
SUM(CASE WHEN Days = 6 THEN count1 ELSE 0 END) AS Day7
FROM (SELECT DATEDIFF(day, @.BaseDate, Date1) AS Days,
DATEDIFF(minute, @.BaseDate, Date1) / 5 % 288
AS FiveMinIntervals,
count1
FROM tableA) AS d
GROUP BY FiveMinIntervals
Hugo Kornelis, SQL Server MVP