title: Table valued parameters with reference to other tables
body: <p>I have been trying to figure out how to efficiently insert 100000 rows at a time from C# console program to sql server DB. </p>
  
  <ol>
  <li>Select 100000 from input table</li>
  <li>Send each record to an API.</li>
  <li>Results are collected in a List Object</li>
  <li>call a procedure that takes table valued parameter.</li>
  <li>I cannot directly say Insert into empaddr_verified select * from @EMPObj parameter because the verified_table is referencing other tables.</li>
  </ol>
  
  <p>What is the best way to handle this?</p>
  
  <pre><code> create table #input_table(empid [varchar] (10) not null,
  empaddr  [varchar](2000) NULL,[EMpName] [varchar](1000) NULL))
  insert into  empaddr_tobeverified('1','1 main st, salem,pa,USA','JOE STILTON');
  insert into  empaddr_tobeverified('2','200 Baker st, salem,pa,USA','JIMMY WU');
  
  create table Addrstatus(
  statusid [int] IDENTITY(1,1) not null primary key,
  statusDesc  [varchar](2000) not NULL);
  insert into Addrstatus(1,'OK');
  insert into Addrstatus(2,'Not OK');
  
  create table empaddr_verified(
  empid [varchar] (10) not null primary key,
  empalladdr  [varchar](2000) NULL,[EMpName] [varchar](1000) NULL,
  statusid int not null Foreign Key references Addrstatus(statusid))
  insert into empaddr_verified(
      '1'
      ,'1 main st, salem,pa,USA | 100 WHITE Terr, Acton,MA USA'
      ,'JOE STILTON'
      ,(If not exists(
          select statusid 
          from AddrStatus where statusDesc='NOT VERy SURE') 
          INSERT INTO Addrstatus('NOT VERy SURE'))))
  
  
  create table employeeaddress(empid Foreign key references empaddr_verified(empid), empaddr varchar(1000) not null)l
  insert into employeeaddresses('1','1 main st, salem,pa,USA ')
  insert into employeeaddresses('1','100 WHITE Terr, Acton,MA USA ');
  
  CREATE TYPE [testdb].[EMPTBL] AS TABLE(
      [EMpName] [varchar](1000) NULL,
                 [EMpAllAddr] [varchar](1000) NULL,
                 [EMpID] [varchar](10) not NULL) , statusDesc varchar2(100) not null
  
  create procedure usp_test
  (@EMPObj As [testdb].[EMPTBL] Readonly)
  insert into empaddr_verified as select * from @EMPObj 
  </code></pre>
  
  <p>-- works when status id is in AddrStatus table
  -- not sure how to handle when StatusID does not exist in AddrStatus table &amp; how to do it with table valued parameters</p>