SQL bulk insert unique rows from a parent table to multiple child tables

Question

SQL bulk insert unique rows from a parent table to multiple child tables

Old Hand

Points: 334

June 20, 2020 at 11:31 am

I have a table Locations whose schema is below. I want to select all unique rows from the Home table and **bulk** insert into locations and individual table

**Home Table**

     - Id (identity)
     - LocationGroupId (can be null)
     - IndividualGroupId (can be null)
     - Address
     - City
     - State    
     - Zip
     - FirstName
     - LastName

**Home Table**

     - id | locationgroupid | individualgroupid | Address.. | FirstName...
       1  | 3020            | 3020              | testaddr1 | testname1
       2  | 3020            | 3020              | testaddr1 | testname1
       3  | NULL            | NULL              | testaddr2 | testname2
       4  | NULL            | NULL              | testaddr3 | testname3
       5  | 7832            | NULL              | testaddr4 | testname4
       5  | NULL            | 5643              | testaddr5 | testname5


**Locations**

     - LocationId (identity)
     - LocationGroupId (can be null)
     - IndividualGroupId
     - Address
     - City
     - State    
     - Zip

**Locations Table**

     - id | locationgroupid | Address.. 
       1  | 3020            | testaddr1       
       2  | NULL            | testaddr2 
       3  | NULL            | testaddr3 
       4  | 7832            | testaddr4 
       5  | NULL            | testaddr5 
    
**Individuals**

     - IndividualId (identity)
     - IndvidualGroupId(can be null)
     - FirstName
     - LastName

**Individuals Table**

     - id | individualgroupid | FirstName...
       1  | 3020              | testname1     
       2  | NULL              | testname2
       3  | NULL              | testname3
       4  | NULL              | testname4
       5  | 5643              | testname5

This topic was modified 5 years, 2 months ago by marcus.
This topic was modified 5 years, 2 months ago by marcus.
This topic was modified 5 years, 2 months ago by marcus.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

If you would like a coded solution, please provide sample table structure DDL, sample data in the form of INSERT statements for your sample table, and desired results based on the sample data provided.

marcus Old Hand Points: 334 More actions · Answer 2

I updated the question details and provided the sample data.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

marcus wrote:

I updated the question details and provided the sample data.

If I cut and paste from your answer into SSMS, I get syntax errors. I see no DDL or DML. You're not new to these forums, you should know what's required. But if you don't, please check out the following link – it explains how to do it:

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help