DTS Package and ASP.NET

Hi all,

I need to export tables out of a Pervasive DB and into SQL Server 2K. I
have set up a DTS Package to do this when a user visits a web page
(which will then allow them to view a up to date report using MS
Reporting Services).

Currently my DTS package checks to see if the table exists in SQL
Server and then drops it, creates it, and then does the import of data
from Pervasive.

I am wondering if there is a way to get only the new records? There are
currently no PK defined (Pervasive does not make use of them). However,
I noticed that the DTS package can assign PK.

Does anyone have any advice/code snippets?

I greatly appreciate your help.

Thanks,
TonyHi

What do you do if the existing record has been updated? Even if there is no
primary key there should (hopefully!) be a unique way (set of columns) to
identify them, this should be the PK in the SQL Server database.

One method is to load into a staging table then work from there. If you
drive the transformation using a SQL command in the form
SELECT S.Col1, S.Col2, ...
FROM StageTable S
WHERE NOT EXISTS ( SELECT 1 FROM DestinationTable D WHERE D.PK = S.PK )

You will not insert the existing rows. You can also use an additional SQL
Command step before the insert to update existing rows.

UPDATE D
SET Col1 = S.Col1,
Col2 = S.Col2
FROM DestinationTable D
JOIN StageTable S ON D.PK = S.PK
WHERE D.Col1 <> S.Col1
OR D.Col2 <> S.Col2

John

"Tony" <tcarcieri@.rihousing.com> wrote in message
news:1109857630.729309.129570@.l41g2000cwc.googlegr oups.com...
> Hi all,
> I need to export tables out of a Pervasive DB and into SQL Server 2K. I
> have set up a DTS Package to do this when a user visits a web page
> (which will then allow them to view a up to date report using MS
> Reporting Services).
> Currently my DTS package checks to see if the table exists in SQL
> Server and then drops it, creates it, and then does the import of data
> from Pervasive.
> I am wondering if there is a way to get only the new records? There are
> currently no PK defined (Pervasive does not make use of them). However,
> I noticed that the DTS package can assign PK.
> Does anyone have any advice/code snippets?
> I greatly appreciate your help.
> Thanks,
> Tony|||Hello John,

Thanks for the reply.

Is this scenario possible (psuedo code)?

If table exists
1)run update on exisiting data
2)select from pervasive db where not exists
Else
1)create table
2)run select

Your thoughts?

Thanks so much!
Tony

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Tony

I am not sure why you check the table existance, if you are in control
of the site then it should be know to exist or be part of an
installation. As previously stated, you can drive the data population
from a SQL statement, but doing this over your network may mean that
loading into a staging table may be quicker.

A different way to do this would be though a stored procedure and a
linked server.

John

Tony Carcieri wrote:
> Hello John,
> Thanks for the reply.
> Is this scenario possible (psuedo code)?
> If table exists
> 1)run update on exisiting data
> 2)select from pervasive db where not exists
> Else
> 1)create table
> 2)run select
> Your thoughts?
> Thanks so much!
> Tony
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!