DTS with multiple files (merge)

Posted on 2003-02-25
Medium Priority
Last Modified: 2013-11-30
I am using DTS to import from CSV files into a table on MS SQL doing some transformations on the way.
My problem is that I would need to import data from different files that are matched row to row but with different fields.

File1                  TableMerge
FieldA FieldB FieldC   Col1 Col2 Col3

File2                  TableMerge
FieldA FieldB FieldZ   Col4 Col5 Col7

What I would like to do is process everything in parallel. Basically the two files are opened (same number of rows)and data is merged into the table row by row.
However DTS processes things sequentially so I have rows that are totally mismatched. Any suggestion/workaround greatly appreciated...

Question by:ximox-TFI
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8022411
try to use DTS to load the two files into two intermidiate tables. when they are in tables, you can do any kind of merge as you want.

Accepted Solution

Danielzt earned 300 total points
ID: 8022477
let's say file1 into table1, file2 into table2.

create table table3
(fieldA ...,FieldB ..,FieldC..,FieldZ...)
insert into table3
select fieldA,FieldB,FieldC,FieldZ
from table1,table2
where table1.FIELDA=table2.FIELDA and table1.FIELDB=table2.FIELDB

create table table4
insert into table4
select col1,col2,col3,col4,col5,col7
from table1,table2
where table1.FIELDA=table2.FIELDA and table1.FIELDB=table2.FIELDB


Expert Comment

ID: 9276331
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question