Insert values from one table into multiple tables

Question

Insert values from one table into multiple tables

fparker 20089

SSC-Addicted

Points: 425
More actions
March 23, 2018 at 11:30 am

#330059

I need a little help. I have a supplied file that contains many company names and addresses. I need to break this up and insert the values from the single table or file into multiple tables using SQL. It could be any combination of queries, functions, stored procedures, etc. I am using unique identifiers in each table that are system generated. The basic layout is is 5 tables for the target.
TIA
[PARTY_NUMBER]
  ,[PARTY_NAME]
  ,[ADDRESS1]
  ,[ADDRESS2]
  ,[ADDRESS3]
  ,[ADDRESS4]
  ,[CITY]
  ,[STATE]
  ,[POSTAL_CODE]
  ,[PROVINCE]
  ,[COUNTRY]
The CompanyMaster will get the Party_Name as will the Company.
The Account will get the Party_Number
The Address will get the Party_Name, City, Postal_Code or Province and Country
The Address Line will get each of the addresses, 1-4 in the Value field.

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

I don't see any reason to break this up at all. One table is sufficient to hold a given address. Just the extra query complexity of having a separate table to handle any additional address lines would quickly outweigh any gains in disk space.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

fparker 20089 SSC-Addicted Points: 425 More actions · Answer 2

It's about master data. It is a similar format to the target system. Unfortunately the source systems are in multiple formats and I need to get to a single format that is somewhat flexible.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

fparker 20089 - Tuesday, March 27, 2018 1:06 PM
It's about master data. It is a similar format to the target system. Unfortunately the source systems are in multiple formats and I need to get to a single format that is somewhat flexible.

Okay, I'd probably use SSIS, with a Multi-Cast to effectively copy the entire data stream to as many paths as you need, and just map the appropriate data columns to the various Destinations as needed, with one destination per path. Alternatively, you could funnel it all into a temp table, and then run queries from the temp table that individually insert into the various tables. There's a gain with SSIS in that all of those destinations could be getting populated at the same time, whereas the query would just do one at a time.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

fparker 20089 SSC-Addicted Points: 425 More actions · Answer 4

Great thanks. I haven't done SSIS in a few years but it shouldn't be that hard.