Test stored procedure with user defined table type from data in csv file

Question

I have a stored procedure that accept a UDTT(User Define Table Type). I'd like to test the performance using data input from csv files. Since the store procedure handles foreign key relationship, I will not use SQL build-in bulk inserts. How to do this in SQL management studio?

Answer 1

Here is the steps that I found works:

1. BCP the data from csv file to a temp table.

   bcp TempDb.dbo.CsvTest in "C:\test.csv" -T -c -t ,

2. Use the temp table to populate the UDTT

   INSERT INTO @args

   SELECT col1, col2 FROM TempDb.dbo.CsvTest

   EXEC @return_value = [dbo].[myProcedure] @inputs = @args

Not sure if there is a way to skip the temp table.