How to Combine Two Tables

Question

How to Combine Two Tables

Will Strohl

Grasshopper

Points: 24
More actions
April 6, 2005 at 8:53 am

#67382

I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bill Geake Ten Centuries Points: 1352 More actions · Answer 1

If I understand it correctly, you want to retrieve certain records from the second flat file and append them to the table populated from the first file.

I would:

Build a table to load the second flat file into
Have a transformation task which loads this data from the file as-is
Write a stored procedure to return the desired records from the new table
Have a second transformation task which uses the stored procedure as a source and the existing table as the destination. This would deal with the missing / out of order columns.
Schedule by right-clicking the DTS package in Enterprise manager. Then go to SQL Server Agent in EM to adjust the new job as needed.

HTH

Bill.

Will Strohl Grasshopper Points: 24 More actions · Answer 2

Thank you for your reply. Through much trial and error yesterday, I eventually did something very similar to what you suggested. However, I have it all in a single DTS package.Thank you so much for your reply.

amackay_1 Ten Centuries Points: 1240 More actions · Answer 3

Books Online is a pretty good source of DTS information. Search under "DTS, transformation issues" to get some specifics to watch for when setting up your process.