Last post Mar 15, 2018 09:10 AM by 1jus
Mar 13, 2018 09:38 AM|1jus|LINK
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).
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 :)
Mar 13, 2018 02:04 PM|limno|LINK
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.
Mar 13, 2018 02:58 PM|1jus|LINK
Thanks for taking the time to reply.
Conceptual data is as follows:
I need to insert each piece of data into its own table
Mar 13, 2018 04:03 PM|limno|LINK
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.
Mar 14, 2018 12:50 AM|oned_gk|LINK
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
Mar 14, 2018 08:13 AM|jimmy69|LINK
@1jus => i'm not an expert but few time ago i have a task as this.
i have do this:
it's not prehpas the right solution or the solution who take less time to realize but it's worked for me
Mar 14, 2018 09:05 AM|1jus|LINK
@limno Therein lies the problem, my SQL knowledge is quite limited! Thanks for trying to help though :)
@jimmy69 Thanks for your ideas. Much appreciated
Mar 14, 2018 01:38 PM|limno|LINK
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.
Mar 14, 2018 04:17 PM|1jus|LINK
@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.
Mar 14, 2018 05:43 PM|limno|LINK
You can merge all sql script in one file and execute together in one run.
Mar 15, 2018 09:10 AM|1jus|LINK
@limno Excellent, thanks very much. Process now runs in around <5 seconds which is great! :)