Do multiple inserts per row in a table

Hi,

I'm trying to do a data migration importing one table to multiple tables and need to insert multiple rows into other tables.

Basically I need:

for each row in [tablename]
  insert in table1 with values from the [tablename]
  insert in table2 with values from the [tablename]
  insert in table3 with values from the [tablename]
loop
LVL 2
prositAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Throw it all in a transaction, so all INSERTs succeed or fail together.

BEGIN TRAN
   BEGIN TRY
      INSERT INTO Table1 SELECT * FROM tablename
      INSERT INTO Table2 SELECT * FROM tablename
      INSERT INTO Table3 SELECT * FROM tablename

   --- If code execution makes it here, good to go. 
   COMMIT TRAN
    END TRY

BEGIN CATCH 
   -- IF code execution makes it here, it threw an error, so kill the transaction. 
   ROLLBACK TRAN
END CATCH

Open in new window

0
prositAuthor Commented:
JimHorn,

Ok, well there's another catch...

In the table to be imported I need to copy some information, but in the other tables I need to add a link to that row, so they're linked together:

for each row in [tablename]
  Set UID = new()
  insert in table1 UID + values from the [tablename]
  insert in table2 UID + values from the [tablename]
  insert in table3 UID + values from the [tablename]
loop

I don't think your suggestion will resolve that.

~j
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'link to that row', preferably with some mockup data and expected results.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

prositAuthor Commented:
Ok,

origin table:

PHINFO:

Control, Suffix, Account, RRName, Contact, Address1, Address2.

So I need to send the Control, Suffix and Account to one table, and the address part to another table:

PolicyHolderInfoTable:
UID (uniqueID), Control, Suffix, Account, AddressGUID (pointing to the address table)

AddressTable:
UID (UniqueID matching the PolicyholderInfoTable AddressGUID), RRName, Contact, Address1, Address2

Does this make any better sense?

I truly appreciate your help!

~j
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If we're talking sending different columns to different tables, and grabbing the automagically-generated ID from the table 1 insert and using it in the table 2 insert, then same transaction code as above, but for table 2..n inserts you're going to need to JOIN on the previous table to grab the id.  Something like..
INSERT INTO Table1 SELECT * FROM tablename

INSERT INTO Table2 
SELECT t.*, t1.id
FROM tablename t
   JOIN Table1 t1 ON t.AddressGUID = t.AddressGUID -- or whatever columns can be used to relate

Same dang deal with Table3

Open in new window


If this were a one time deal (i.e. Table2 uses Table1 columns, no table 3) then you could consider the OUTPUT clause.  But not for multiple tables.
0
prositAuthor Commented:
Ok, well I might not be understamding that, but I don't have anything to originally tie them together from the table other than the original row.

OrigTable:
-------------

Control
Suffix
Account
RRName
Address1
Address2

Needs to be split into two tables:

PolicyHolder:
-----------------

UID (uniqueidentifier)
Control
Suffix
Account
AddressID (uniqueidentifier)

Address:
-----------

AddressiD (UniqueIdentifier (linked to PolicyHolder.AddressID)
RRName
Address1
Address2

I might be staring myself blind at this problem... :(
0
prositAuthor Commented:
I guess the original problem was poorly described, i'll open a new question with the proper explanation and the example above.

~j
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but I don't have anything to originally tie them together from the table other than the original row.
Chances are there is a unique identifier in tablename.
If not, you may have to JOIN on all rows.

Are there any duplicate rows in tablename?
0
prositAuthor Commented:
No duplicates on the control sufix account no, on the address, yes there would be.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure what you mean with the above comment.  If the above JOIN comment didn't get you to where you need to be, then provide us some more mockup data that explains this.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prositAuthor Commented:
I was able to resolve it by a mix of comments using temp tables.  

Thank you for  helping out!

~j
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Good deal.  Thanks for the grade, good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.