locked
How to add new data to an existing table from an external data source RRS feed

  • Question

  • I have a table for which I get new data on a monthly basis. The monthly data is an external file with columns separated by a tic (`) mark. The existing table has a primary key column called INVOICE_ID.

    How do I add this external data file to an existing table and what happens I attempt to add a record that already exists on the table? I would like the duplicate record to be written to a new table while the good records get written to the correct table.

    Is there a solution to my task?

    Thanks.
    Dave
    • Moved by Phil Brammer Wednesday, October 28, 2009 12:26 PM SSIS is a better fit for this question (From:Transact-SQL)
    Tuesday, October 27, 2009 10:38 AM

Answers


  •     Here there is no hardcode rule to take exact column names, you can proceed with default columns (column1, column2.. ). If you are confusing by using these names, you can change these names in your FlatFile Source (assuming that your external file is a flat file). By right clicking on your flatfile source component you can select 'Show Advanced Editor' then goto 'Input and Output Properties' under 'FlatFile Source Output' go to 'Output Columns' tab, here by selecting each and every column name and change its name property to some useful name.
    Lakshman
    Wednesday, October 28, 2009 12:04 PM

All replies

  • This specific requirement can be achieved using SSIS packages. Follow the below steps,
    1. Create an SSIS package
    2. Drag and drop Data Flow tasks in the control flow tab. Right click and say edit.
    3. Drag and drop Flat file source and associate a flat file connection manager which will hold the location details of the flat file.
    4. drag and drop a lookup transformaton and check the INVOICE_ID column of the table against the source file column.
    5. If doesnot match populate it to the right table else to a new table.

    This is one way of solving your problem. There could be many other aswell.
    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    • Proposed as answer by arun203 Tuesday, December 1, 2009 5:18 AM
    Tuesday, October 27, 2009 10:43 AM
  • I am very new to tsql and sql server 2005.

    What is SSIS and how do you create an SSIS package?

    Dave
    Tuesday, October 27, 2009 10:54 AM
  • SSIS - SQL Server Integration Services is a platform for building enterprise-level data integration and data transformations solutions. read more here.

    Creating an SSIS Package. click here.



    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    • Proposed as answer by arun203 Tuesday, December 1, 2009 5:18 AM
    Tuesday, October 27, 2009 11:01 AM
  • Bharani,

    OK, I am fumbling around trying to build the SSIS Package. I got my data connection, but how do I get the column names that are on my SQL table onto the data source instead of "column 0", "column 1", etc.? Or does it matter? How about the column attributes? Do I need to assign those as well?

    Thanks again for your help.
    Dave
    Wednesday, October 28, 2009 11:13 AM
  •  
        If your external file already contains column names at the start, then you can retrieve those column names by selecting 'Column names in the first data row' check box in the general tab of Flatfile connection manager. 
    Lakshman
    Wednesday, October 28, 2009 11:26 AM
  • Lakshman,

    My data doesn't have headers.

    Dave
    Wednesday, October 28, 2009 11:50 AM

  •     Here there is no hardcode rule to take exact column names, you can proceed with default columns (column1, column2.. ). If you are confusing by using these names, you can change these names in your FlatFile Source (assuming that your external file is a flat file). By right clicking on your flatfile source component you can select 'Show Advanced Editor' then goto 'Input and Output Properties' under 'FlatFile Source Output' go to 'Output Columns' tab, here by selecting each and every column name and change its name property to some useful name.
    Lakshman
    Wednesday, October 28, 2009 12:04 PM
  • You could bulk copy the data into a staging table using a format file to identify the names of each column:

    BULK INSERT dbo.FromTsv -- Tic seperated values
    FROM 'C:\Data\SrcFile.tsv'
    WITH (FIELDTERMINATOR ='`', DATAFILETYPE = 'char', FORMATFILE='C:\Data\SrcFileFormat.xml');

    Then you can insert to the target:

    INSERT INTO dbo.TargetTable (col1, col2, ...)
    SELECT col1, col2, ... FROM dbo.FromTsv AS ft
    LEFT JOIN dbo.TargetTable tt ON ft.Col1=tt.Col1 WHER tt.Col1 IS NULL;
    GO

    Given that Col1 is the unique identifier or primary key this will insert only rows that aren't already in dbo.TargetTable.


    You can find more about using this method at: https://msdn.microsoft.com/en-us/library/ms188365.aspx
    HTH, Joe


    • Edited by Joe S Torre Saturday, January 30, 2016 12:19 AM
    Saturday, January 30, 2016 12:15 AM