Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    16

    Unanswered: Import Excel into 2 related tables

    I have 1 Excel spreadsheet. It has about 50 fields. I need to import this into my Database.
    The problem is; there are 2 tables that I need to import this into. These 2 tables are related with [ID].

    So, I need to split the data from 1 spreadsheet between 2 SQL server table. and keep the records related.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Yep. I suppose your question is to how to go about this? My question is do you need to do this once or over and over? I assume your database is MS SQL Server because you are posting in this forum. If the version is 2005 or later it will have shipped with SQL Server Integration Services and bcp. If it is before. it should include Data Transformation Services and bcp. There is also the light weight Import Export Wizard. You can use any of these tools to physically move data into a database. I think your question relates more to how to keep the data related with this "ID" column. That all depends on whether this ID column is a system generated IDENTITY column or not. You can check that by executing sp_help <your table name> in a query window in SQL Server Management Studio. Once you do that we can have a better idea what kind of solution to provide you.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2014
    Posts
    16

    Reply

    Hi, Yes to most of your questions

    This is SQL Server 2012.

    It is only a once of (I HOPE 0

    The ID column is a generated ID, So if I should try to import to the one table and then to the other, I would not know what the ID is for the records in the foreign table.

    I know about the Import wizard, but as far as I can see, it only allows me to import one sheet to one table.

  4. #4
    Join Date
    Jan 2014
    Posts
    16

    Replay

    Hi, I looked at the two tables. I am attaching two screen dumps
    Attached Thumbnails Attached Thumbnails screen 1.png   screen 2.png  

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What your are doing is a basic E (extract) T (transform) L (load) operation. M$'s tool for that is SQL Server Integration Services (SSIS). You can create SSIS packages in SQL Server Data Tools in SQL Server 2012. A basic concept of ETL is too load data into a Staging table(s) first. I would build a staging table and use SSIS to iterate through your Excel Sheets to import data into that Staging table. I would make sure that Staging table the incoming data has some kind natural or surrogate key on it. I would then write a stored procedure that inserted into the Parent table (the one that produces the ID), and use the new OUTPUT clause to save off the natural or the surrogate key for your spreadsheet along with the new generated ID column to a table variable and then I would use that table variable to drive my population of the child table. I suggest if your are new to these tools, that you complete the tutorials in SQL Server Books Online.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •