April 16, 2014 at 7:12 am
Hi everyone.
Is it possible to have a procedure like this
create procedure usp_test
(@EMPObj As [testdb].[EMPTBL] Readonly, @ADRObj as [testdb].[AddressTBL] readonly)
-- using cursor I iterate thro table
-- insert into employee select empname,empid,(select statusid from AddrStatus where statusDesc=@statusDesc) from @empobj
--insert into employeeaddresses select * from @AdrObj
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 TYPE [testdb].[AddressTBL] AS TABLE(
[EMPID] [varchar] (10) not null,
[EMpAddr] [varchar](1000) NULL)
create table empaddr_tobeverified(
empid [varchar] (10) not null,
empaddr [varchar](2000) NULL,[EMpName] [varchar](1000) NULL)
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))
create table Addrstatus(
statusid [int] not null primary key,
statusDesc [varchar](2000) not NULL);
My Emp Record can have multiple addresses. This procedure is called by C# program.
I select 10000 records at a time from empaddrtobeverified table from C# program and want to insert into empaddr_verified as efficiently as possible.
Thanks
Rash
April 16, 2014 at 7:20 am
rash3554 (4/16/2014)
Hi everyone.Is it possible to have a procedure like this
create procedure usp_test
(@EMPObj As [testdb].[EMPTBL] Readonly, @ADRObj as [testdb].[AddressTBL] readonly)
-- using cursor I iterate thro table
-- insert into employee select empname,empid,(select statusid from AddrStatus where statusDesc=@statusDesc) from @empobj
--insert into employeeaddresses select * from @AdrObj
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 TYPE [testdb].[AddressTBL] AS TABLE(
[EMPID] [varchar] (10) not null,
[EMpAddr] [varchar](1000) NULL)
create table empaddr_tobeverified(
empid [varchar] (10) not null,
empaddr [varchar](2000) NULL,[EMpName] [varchar](1000) NULL)
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))
create table Addrstatus(
statusid [int] not null primary key,
statusDesc [varchar](2000) not NULL);
My Emp Record can have multiple addresses. This procedure is called by C# program.
I select 10000 records at a time from empaddrtobeverified table from C# program and want to insert into empaddr_verified as efficiently as possible.
Thanks
Rash
The beginning of your post you asked about creating a procedure. It is very unclear what you want that procedure to do. It is at least clear that you do NOT need a cursor for this. You went to the trouble to create used defined table types, don't kill your performance by then using a cursor.
It seems to me that you don't need two tables for this process at all. All you need is a single table EmployeeAddress that has a bit column name IsVerified. I hope that your real tables do not have all the address components jammed into a single column.
If you are stuck using multiple tables instead of one we need a little explanation of what you want this procedure to do. We also could use some sample data in the form of insert statements so we can work on the code.
_______________________________________________________________
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/
April 16, 2014 at 7:46 am
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');
insert into empaddr_verified('1','1 main st, salem,pa,USA | 100 WHITE Terr, Acton,MA USA',1)
-- means that employee 1 works at 2 addresses
insert into employeeaddresses('1','1 main st, salem,pa,USA ')
insert into employeeaddresses('1','100 WHITE Terr, Acton,MA USA ')
insert into Addrstatus(1,'OK');
insert into Addrstatus(2,'Not OK');
My C# program selects 10000 from empaddr_tobeverified send to an API and for every verified record I need to insert a empaddr_verified, and multiple employeeaddresses. One way is to send all parameters for each record i.e. 10,000 calls to database. This is consuming lot of I/O. I was trying to do this by table valued parameter.
April 16, 2014 at 8:00 am
rash3554 (4/16/2014)
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');
insert into empaddr_verified('1','1 main st, salem,pa,USA | 100 WHITE Terr, Acton,MA USA',1)
-- means that employee 1 works at 2 addresses
insert into employeeaddresses('1','1 main st, salem,pa,USA ')
insert into employeeaddresses('1','100 WHITE Terr, Acton,MA USA ')
insert into Addrstatus(1,'OK');
insert into Addrstatus(2,'Not OK');
My C# program selects 10000 from empaddr_tobeverified send to an API and for every verified record I need to insert a empaddr_verified, and multiple employeeaddresses. One way is to send all parameters for each record i.e. 10,000 calls to database. This is consuming lot of I/O. I was trying to do this by table valued parameter.
A looping mechanism is not going to make this better. This goes back to the issue I was discussing about having multiple tables.
Thanks for the sample data but it is not clear what you are trying to do here. Do you pick up all the rows in the ToBeVerified table and send those to another process that validates them or something?
_______________________________________________________________
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/
April 16, 2014 at 8:07 am
Yes, all records from tobeverified needs to be sent to an API for address validation. I want to insert 10,000 records into verified tables as efficiently as possible.
April 16, 2014 at 8:21 am
rash3554 (4/16/2014)
Yes, all records from tobeverified needs to be sent to an API for address validation. I want to insert 10,000 records into verified tables as efficiently as possible.
Not giving me much to go on here...
insert into empaddr_verified ([Columns])
select [Columns]
from YourTableType
_______________________________________________________________
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/
April 16, 2014 at 8:39 am
@SSChampion,
But how would I insert into employeeaddresses table? Can I have 2 table valued parameters as input to a procedure is my question? one to insert empaddr_verified using EMPTBL second to insert employeeaddresses using AddressTBL. Because one employee record can have multiple addresses. I have to do this for 10,000 records.
April 16, 2014 at 9:34 am
rash3554 (4/16/2014)
@SSChampion,But how would I insert into employeeaddresses table? Can I have 2 table valued parameters as input to a procedure is my question? one to insert empaddr_verified using EMPTBL second to insert employeeaddresses using AddressTBL. Because one employee record can have multiple addresses. I have to do this for 10,000 records.
Using a join?
I can't see what you see and have no idea what you are really trying to do here. I am guessing based on very limited information.
Something like this?
insert into empaddr_verified ([Columns])
select [Columns]
from YourTableType
join YourOtherTableType on [SomeCondition]
_______________________________________________________________
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/
April 16, 2014 at 9:35 am
You really haven't provided enough detail to really help you solve the problem you seem to be having. To help us help you we need the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (in the form of INSERT INTO statements or using the row constructor version) for the table(s) involved, the expected results based on the sample data, and in this case the stored procedure you are trying to improve.
For help with this, please read the first article I reference below in my signature block. It will walk you through what you need to post and how to post it. The more information you provide the better answers you will get in return.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply