locked
Select from a single table and insert in to multiple tables? RRS feed

  • Question

  • User439975351 posted

    Hi all, I need some help from you SQL gurus please! I'm pretty good with c# but feel that my requirement may be better catered for using SQL.

    I have a legacy database that I need to migrate so my requirement is to select all data from an existing customer table and insert the data across multiple tables in the new database. As part of this process I need to generate a GUID and then select that GUID for the remaining tables to be populated (if that makes sense).

    Example:

    New Tables

    Customer, Address, LegacyData

    Insert into Customer with new generated GUID, select the newly created Customer record, use its GUID to then insert into the Address table, I then need to select the address records ID and populate a further "mapping" table to associate the address with the customer. This needs to be executed for all data in the legacy customer table. So one insert must follow with the sub inserts then on to the next record.

    I've tried to Google this but its a difficult one to put into words!

    Any direction would be helpful please :)

    Tuesday, March 13, 2018 9:38 AM

Answers

  • User439975351 posted

    @limno Thanks again for helping. I have spilt the export into separate SQL scripts and collated these in a SQL Job to run one after another. It seems to be working pretty well. My question was really whether there was a cleaner more concise way to aggregate the SQL i have written into a single script but that's where my knowledge gets a bit cloudy! For now the SQL job is providing an expedited solution.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 4:17 PM
  • User77042963 posted

    You can merge all sql script in one file and execute together in one run.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 5:43 PM

All replies

  • User77042963 posted

    When you work with SQL, it will be easy to understand what you need with sample table DDL and sample data in the form of script (inserts). You can let us know your expected result from your sample data for these target tables. (the table structures from DDL may provide information about their relationship). Can you try again to repost your question? Thanks.

    Tuesday, March 13, 2018 2:04 PM
  • User439975351 posted

    Thanks for taking the time to reply.

    Conceptual data is as follows:

    Column Data
    Id 1
    Name Mark
    Address 1 Street
    Points 25

    I need to insert each piece of data into its own table

    Customer

    Column Data
    Guid NewID()
    Name Mark
    AddressId <to be populated from subsequent address table insert>

    Address

    Column Data
    Id PK  - IsIdentity
    Address 1 Street

    Points

    Column Data
    Id PK - IsIdentity
    CustomerGuid <Guid from previously inserted Customer Table Guid>
    Points 25

    Tuesday, March 13, 2018 2:58 PM
  • User77042963 posted

    You have to translate your conceptual  sample into script since we are using SQL. (this is a language like you would with C# for your application). 

    The DDLs include create table scripts, key information etc.  Sample data are in the form of inserts script for these tables. Mock up a sample based on your requirement. When you have your question in this form, you are almost there. You will get some suggestions for what you need much quicker. We can test run any queries we can come up with for your  need.

    Tuesday, March 13, 2018 4:03 PM
  • User-1716253493 posted

    Simply include current key (maybe temporary) when insert data into new table,

    Then use the key, if you want to update another columns datas, or insert new generated new guid into new another table

    Wednesday, March 14, 2018 12:50 AM
  • User632428103 posted

    Hell all,

    @1jus => i'm not an expert but few time ago i have a task as this.

    i have do this:

    • insert all record into a temporary table with a new field (example name) IsTreated = 0
    • make a while with a select top on this temporary table where IsTreated = 0
    • browse over each row get value name, street, options make a PRINT for example to see result
    • and after check your requirement (ex : if Name Marc exist in customer don't insert it but get Id for inserted into address and points
    • and so on ...

    it's not prehpas the right solution or the solution who take less time to realize but it's worked for me

    Wednesday, March 14, 2018 8:13 AM
  • User439975351 posted

    @limno Therein lies the problem, my SQL knowledge is quite limited! Thanks for trying to help though :)

    @jimmy69 Thanks for your ideas. Much appreciated

    Wednesday, March 14, 2018 9:05 AM
  • User77042963 posted

    It is easy to generate a table DDL script if you are using SSMS. Right click on your table name>>Script table as >>Create to  ..New query Window... you will have your table script ready.

    You can also generate both table structure and data at the same time by:right click on your database name>>Tasks>>Generate Scripts...>>Choose Objects   choose your table >>Click on Advance tab to pick  Typesof data to script to Schema and data. Click on OK.  You will have scripts for both your table and data.

    You can modify these script to present your question. 

    Wednesday, March 14, 2018 1:38 PM
  • User439975351 posted

    @limno Thanks again for helping. I have spilt the export into separate SQL scripts and collated these in a SQL Job to run one after another. It seems to be working pretty well. My question was really whether there was a cleaner more concise way to aggregate the SQL i have written into a single script but that's where my knowledge gets a bit cloudy! For now the SQL job is providing an expedited solution.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 4:17 PM
  • User77042963 posted

    You can merge all sql script in one file and execute together in one run.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 5:43 PM
  • User439975351 posted

    @limno Excellent, thanks very much. Process now runs in around <5 seconds which is great! :)

    Thursday, March 15, 2018 9:10 AM