469,112 Members | 2,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

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,
Tony

Jul 23 '05 #1
3 1206
Hi

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" <tc*******@rihousing.com> wrote in message
news:11**********************@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

Jul 23 '05 #2
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!
Jul 23 '05 #3
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!


Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Petterson Mikael | last post: by
10 posts views Thread by datapro01 | last post: by
6 posts views Thread by Page Horton | last post: by
3 posts views Thread by shorti | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
reply views Thread by Steven Samuel Cole | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.