Reference table with table valued parameter

• rash3554

  SSCommitted

  Points: 1984

  More actions

  April 17, 2014 at 4:35 am

  #285879

  Hi there,

  I have been trying to figure out how to efficiently insert 100000 rows at a time from C# console program to sql server DB.

  1. Select 100000 from input table

  2. Send each record to an API.

  3. Results are collected in a List Object

  4. call a procedure that takes table valued parameter.

  4. I cannot directly say Insert into empaddr_verified select * from @EMPObj parameter because the verified_table is referencing other tables.

  What is the best way to handle this?

  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

  -- works when status id is in AddrStatus table

  --not sure how to handle when StatusID does not exist in AddrStatus table & how to do it with table valued parameters

  Thanks

  Rash

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 17, 2014 at 8:37 am

  #1706790

  Starting a new thread on the same topic with the same lack on information is not going to provide a better answer. You have to keep in mind that we can't see your screen, have no idea what your project is supposed to do. The only details we have are what you have posted. In your other thread we have asked for some clarification. If you provide enough details for us to help this should be pretty simple.

  http://www.sqlservercentral.com/Forums/Topic1562280-391-1.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/