Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions 
Author Message
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions
Hi all,
I create a dts package than import a csv file in an existing table. I
would like dts to do the following :for each row in the csv file
1)append it in the table if the data doesn't exist in the destination
table,
2)update the the table value if the data exists int the destination
table.
Does dts allow this kind of treatement?
Any help is welcome.
Thks


Mon, 28 Feb 2005 05:19:13 GMT
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions

Yes

Most (99.9%) of people BULK INSERT the csv file into a scratch SQL
Server table first before trying to manipulate it as SQL Server is a
lot more flexible than a text file.

Once in there you can then do comparisons on key values

I.E

To decide if you need to insert

"Do I have any key values in the scratch tabole that are not in the
Proper Destination"

To decide on an update

"Do I have any keys in the scratch table that are in the proper
destination ?  Is so I'll use a lookup and do an update"

You can even just use 2 ExecuteSQL tasks for this

--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com



Tue, 01 Mar 2005 02:47:03 GMT
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions

Quote:

> Yes

> Most (99.9%) of people BULK INSERT the csv file into a scratch SQL
> Server table first before trying to manipulate it as SQL Server is a
> lot more flexible than a text file.

> Once in there you can then do comparisons on key values

> I.E

> To decide if you need to insert

> "Do I have any key values in the scratch tabole that are not in the
> Proper Destination"

> To decide on an update

> "Do I have any keys in the scratch table that are in the proper
> destination ?  Is so I'll use a lookup and do an update"

> You can even just use 2 ExecuteSQL tasks for this

Thks a lot Allan,
I found a tutorial that you wrote http://www.sqldts.com/default.aspx?6,107,277,7,1
I will try your example scenarios.
Thks.


Tue, 01 Mar 2005 23:25:19 GMT
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions
Hi all,
   thks allan , I read your tutorial and now I'm tryng an example of
my own.
In my example I have two tables belonging to 2 differents
databases(source and destination), the two tables have the same
structure :
AccSrc(CustomerID nchar(5) NOT NULL primary key,CompanyName nvarchar
(40) NULL)
AccDest(CustomerID nchar(5) NOT NULL primary key,CompanyName
nvarchar(40) NULL)
My scenario is the following :
For each of row in source table AccSrc
        if the row exists in the destination table AccDest then
            update the value of companyName in table AccDest
        else
            insert the row in AccDest
        end if
end for
This is very simple to do by using simple transact sql statement and
cursors. But I would like to use DTS:
I understand the concepts behind DDQ ,Lookups , Active Scripts: but
the fact is than I dont know how to apply them in my example. HOW DO I
LINK ALL THESE OBJECTS TO HAVE MY RESULTS?
Please help.


Sat, 05 Mar 2005 03:31:01 GMT
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions
Ahhh

The table definition and explaation made the world of difference
thankyou.

One thing to be aware of is that althought DTS moves data it is not
always the best hammer to hit the nail with.  In some cases a simple
TSQL statement will do the job much quicker as it will not involve
loading DTS into memory.

To your Example.

Sample solution  1

2 lookups

1.  SELECT COUNT(*) FROM DestTable WHERE KeyField = ?
2.  UPDATE DestTable SET col1 = ?.......

In the AX transform

dim cnt

cnt = DTSLookups("IsThereAnyrecords").Execute("DTSSource("KeyCol"))

if cnt <> 0 then
   DTSLookups("UpdateTheRecord").Execute DTSSource("colWhatever")......
   Main=DTSTransformStat_SkipInsert
Else
   DTSDestination("Coln") = DTSSource("coln")
   ...
   Main = DTSTransaformStat_OK
End if

Sample 2

Use Linked Server (inserts)

Source Statement = SELECT <ColList> FROM
OPENQUERY(<linkedServerName>,'SELECT * FROM Database.owner.Table') A
LEFT OUTER JOIN LocalTable B ON A.KeyCol = B.KeyCol
WHERE B.KeyCol IS NULL

Use Linked Server in ExecuteSQL task for Updates as well.

--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com



Sat, 05 Mar 2005 05:25:11 GMT
 Dts Import Question:Importing Csv file in an existing SQL2000 table with row-level conditions
Thks a lot Allan,
I finally chose the lookups solution and everything works perfectly.
Thks
bye


Wed, 09 Mar 2005 02:20:07 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Importing a 66 gigabyte csv file into SQL2000 using DTS

2. Importing "csv" file into existing table

3. Importing CSV file to Existing Access DB

4. Importing/Merging Text File (csv) into existing DB (*.mdb)

5. Importing unicode CSV file via DTS

6. SQL 7.0 DTS Import of CSV files

7. DTS import csv - dynamic file name

8. Importing unicode CSV file via DTS

9. DTS-CSV import file has dangling comma at the end of the record

10. SQL 7.0 DTS Import of CSV Files

11. DTS import error from CSV file - cannot insert value NULL

12. SQL 7.0 DTS Import of CSV files


 
Powered by phpBB® Forum Software