none
SQL Server - Howto add Data to Table from Text File

    Question

  • Hi

    I am trying to rewrite a DTS package from sql server 2000 so that it may function with sql server 2008 r2. It should be a very basic program to write, but for some reason I just can't get it right. I need to read data from a text file into a temporary table. This I am able to do. It then needs to compare this temporary table with the current production table containing similar data and only add the records that are not on the production table. Basically the text file contains the latest info or records from an old mainframe type app and it is these updated or new records that has to be added to the production table found within sql server 2008 r2.

    Currently the DTS package is doing this, but I fail to see a way in which I can transform this thing into sql server 2008 r2 format. I have tried to save the DTS package and open it in sql server 2008 r2, but can't get the designer part to open.

    I am now considering writing the whole thing from scratch in C#, but there must be an easier way to do this?

    Karl

    Tuesday, October 11, 2011 8:36 AM

Answers

All replies

  • Hi Karl,

    You can use BULK INSERT in SQL Server 2008 R2 to import data file into a database table.
    Please read the usage of BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx


    Hope this helps.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, October 12, 2011 8:53 AM
    Moderator
  • Hi Karl,

    Here is a novice method for your understanding—

    After you have imported data into the temp table you can compare the data with the destination table as

          select t.*

          from temp tmp left join [destination] dest on (tmp.id = dest.id)

          where dest.id is null

    This will return you the extra records present in the temp table. Now, you can simply insert those records into the destination table as—


    insert into [destination]

    (

          select t.*

          from temp tmp left join [destination] dest on (tmp.id = dest.id)

          where dest.id is null

    )


    ------------------------------ Regards, Biranchi *** Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, October 12, 2011 12:12 PM