sql - Execute stored procedure for each record in table -

Text after "

I need two tables to update, Master and Identifier:

Master
--MasterID (P)
--ModifiedDate
--ModifiedBy

Identifier
--IdentifierID
--MasterID (FK Master)
Identifier < Br> --IdentifierType
--ModifiedDate
--ModifiedBy

The only reason for the existence of the master table is to tie different identifiers for a single person.

I get a file containing new identifier information (Master ID (if available), identifier, identifier type) in the identifier. The records of a MasterID are clearly simple to include; Without a record, however, one becomes a bit more difficult.

A new MasterID will be generated, before entering the data in the identifier table, then it can be used as the FK form.

I was thinking that a stored proc can do some work on the lines of this:

  Announcement @IDOutput INT INSERT INTO Master (ModifiedDate, ModifiedBy) values ​​(GETDATE (), 'Robert' set @IDOutput = SCOPE_IDENTITY () identifier (MasterID, Identifier, IdentifierType, ModifiedDate, ModifiedBy) values ​​(@IDOutput, Identifier, --This input file comes from IdentifierType, --This input file GETDATE ( Comes from), 'Robert')  

My problem is that it is stored for each record in my input file Proc is running I have read that using a cursor, a stored procedure or a temporary table can be a way of handling it in a stored procedure, but I'm not sure that using any one of these methods The best way to implement In fact, I'm not sure if I'm going to go about it in the right way.

Any help in this regard will be greatly appreciated!

After the

Assume that you have a lofty table, then you should see the output volume instead of looping

By using

you can capture multiple keys, then you select the "master" rows from the table table and store the new keys in a table variable. . After this, you can use this to populate the child table

like this:

  insert into the master (ModifiedDate, ModifiedBy, foo) output Inserted.MasterID, Inserted.ModifiedBy Check @NewKeys StagingTable INSERT INTO Identifier (MasterID, Identifier, IdentifierType, ModifiedDate, ModifiedBy), select from G.D. , --This input file comes from GETDATE (), join N.ModifiedBy StagingTable S @SmodifiedBy = N.ModifiedBy on NewKeys N.  

Although I have to say: Your schema Of mnore is. The master table almost does not have any purpose here ...