Multiple inserts using 'User Defined Table Type' (asp.net sql-server)

Question

In my ASP.NET web app I have a DataTable filled with data to insert into tblChildren.
The DataTable is passed to a stored procedure.
In the SP I need to read each row (e.i Loop through the DataTable), change a couple of columns in it (with accordance to the relevant data in tblParents) and only then insert the row into tblChildren.

SqlBulkCopy wouldn't do and I don't think TVP will do either (not sure... not too familiar with it yet).
Of course I can iterate through the DataTable rows in the app and send each one separately to the SP, but that would mean hundreds of round trips to the SqlServer.

I came across two possibilities that might achieve that : (1) Temp table (2) Cursor.
The first is quite messy and the second, as I understand it, is NOT recommended)

Any guidance would be much appreciated.

EDIT :
I tried the approach of user-defined Table Type.

That works because I populate the Table Type (TT_Children) with values in the TT_Child_Family_Id column.
In real life, though, I will not know these values and I would need to loop thru @my_TT_Children and for each row get the value from tblFamilies, something like this :

SELECT Family_Id FROM tblFamilies WHERE Family_Name = TT_Child_Last_Name

(assuming there is always an equivalent for TT_Child_Last_Name in tblFamilies.Family_Name)

So my question is - how to loop through the table-type and for each row look up a value in a different table?

EDIT 2 (the solution) :
As in Amir's perfect answer, the stored procedure should look like this :

ALTER PROCEDURE [dbo].[usp_Z_Insert_Children]
@my_TT_Children TT_Children READONLY
AS
BEGIN
    INSERT INTO tblChildren(Child_FirstName,
                            Child_LastName,
                            Child_Family_ID)
        SELECT Cld.tt_child_FirstName,
               Cld.tt_child_LastNAme,
               Fml.Family_Id FROM @my_TT_Children Cld
                    INNER JOIN tblFamilies fml
                        ON Cld.TT_Child_LastName = Fml.Family_Name
END

Notes by Amir : column Family_Name in tblFamily must be unique and preferably indexed.
(Also I noticed that in case TT_Child_LastName does not have a match in tblFamilies, the row will not be inserted and I'll never know about it. That means that I have to check somehow if all rows were successfully processed).

Why can't you use the stored procedure that you used to populate the DataTable in the first place? — Ross Bush, Feb 03 '16 at 23:34
Possible duplicate of [How to insert a data table into SQL Server database table?](http://stackoverflow.com/questions/9075159/how-to-insert-a-data-table-into-sql-server-database-table) — Ross Bush, Feb 03 '16 at 23:42
Please explain what "change a couple of columns (with accordance to the relevant data in tblParents) means... Best would be to create a [Fiddle](www.sqlfiddle.com). At least provide table structure and what you've tried so far... — Shnugo, Feb 04 '16 at 00:14

score 1 · Accepted Answer · answered Feb 07 '16 at 07:51

You can join tblFamilies into the insert in the procedure and take the value from there. Much more efficient than looping through. Or create a cursor and do one child at a time. 1) Make sure there is only one occurance of FamilyName in tblFamilies. 2) Make sure that if tblFamilies is a large table, then the FamilyName column is indexed.

INSERT INTO tblChildren(Child_FirstName, Child_LastName, Child_Family_ID)
SELECT Cld.tt_child_FirstName,
Cld.tt_child_LastNAme,
Fml.FamilyID
FROM @my_TT_Children Cld
INNER JOIN tblFamilies fml on Cld.TT_Child_LastName = Fml.FamilyName

But be aware that if tblFamilies has more than one entry per Family_Name, then this will duplicate the data. In this case you will need to add more restrictions in the where.

I knew it, I KNEW IT...! I'm just getting into the world of `INNER JOIN`s and I was fooling around with it when you came up with your perfect answer which perfectly works! Thanks a bunch! I'll mark it as an answer and edit the question with your solution (which has a couple of minor field name typos). BTW, Family_Name in tblFamilies is unique and indexed, of course. — gadi, Feb 07 '16 at 09:22

Multiple inserts using 'User Defined Table Type' (asp.net sql-server)

1 Answers1