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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

621 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