PLEASE HELP

Question

PLEASE HELP

Tsql-561486

SSC Eights!

Points: 821
More actions
June 20, 2008 at 3:51 am

#75244

Hi
Please help me
I have to create a SP.
The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)
UserIDAcctIDLevel1level2
test testee N Y
the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.
After checking all the strings ,
it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

sqldba,

first of all try yourself, otherwise you can't learn anything new. Try to write your own SP. Becuase it is a basic one, without knowing how to wite a simple SP, you can't stand in the market.

So my advice to you is try to write your own, if you face any problem then post it here. It is a good and healthy one.

karthik

Tsql-561486 SSC Eights! Points: 821 More actions · Answer 2

Thanks for your advice.

But let me know, how can the strings of data can be revitrieved one by one and check if one of the value (Acctid ) in the string exists in the table.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Why would you ever do that kind of process one row at a time? turning an upsert (which is a pet name for what this kind of operation is) into a procedural, row-by-row is just going to make it take a lot longer to finish, with NO advantages over something set-based.

Tell you what - there are lots of various suggestions already put together about UPSERTS or Incremental Loads floating around. Start by just hitting the search interface and reading up - should give you plenty of ideas on how to do it. Start with this example:

http://www.sqlservercentral.com/Forums/Topic280691-145-1.aspx#bm283020

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

goodguy SSCertifiable Points: 5456 More actions · Answer 4

SQLDBA: You can use the substring function and the semicolon delimiter to "cut up" the string and read the values into an array.

Karthik: With all due respect, sometimes the tone of your reply may come across as condescending, even though you may not have intended it.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Tsql-561486 SSC Eights! Points: 821 More actions · Answer 5

But in this case , the apllication is sending some strings

example

(SVDB, N, Y; SFGB, N, N; SGTE Y, S; SVHG Y,Y; SVFH, Y, Y; ........)

data set in string may vary.

So when the SP is called, the SP has to get the first set i.e (SVDB, N, Y) from the application --- how to get these set into the SP ,

then check if SVDB(this is Acctid) is intable, if yes update else insert

then get the second set (SFGB, N, N), repeat the process till end

Finally check if any other Acctid in table , if yes , delete those rows.

I am not clear, how to get the string from application to SP

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Then before worrying about the merge or upsert, focus on figuring out how to bulk load that data into a temp data used for staging. We've got some BCP kings floating around that should be able to walk you through how to do that (there's a syntax for determining row and column delimiters but it eascapes me right now); SSIS could also easily be used to set up something to read that.

One row at a time is a real perf killer. Really. You can cut a one row at a time from 4-5 hours per run down to a 20-45 sec bulk load process, with ultimately no loss in reliability. Even if you had to add a few minutes worth of extra processing to do a lot of validation, you're still ahead by 4-5 hours...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

karthikeyan (6/20/2008)
sqldba,
first of all try yourself, otherwise you can't learn anything new. Try to write your own SP. Becuase it is a basic one, without knowing how to wite a simple SP, you can't stand in the market.
So my advice to you is try to write your own, if you face any problem then post it here. It is a good and healthy one.

Not sure if that's right on your part, Karthik... how many times did we tell you to look something up only to have you come right out and say that your didn't want to be told to look it up? How many times did you beg for an example of code?

No... not right on your part...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

sqldba (6/20/2008)
But in this case , the apllication is sending some strings
example
(SVDB, N, Y; SFGB, N, N; SGTE Y, S; SVHG Y,Y; SVFH, Y, Y; ........)
data set in string may vary.
So when the SP is called, the SP has to get the first set i.e (SVDB, N, Y) from the application --- how to get these set into the SP ,
then check if SVDB(this is Acctid) is intable, if yes update else insert
then get the second set (SFGB, N, N), repeat the process till end
Finally check if any other Acctid in table , if yes , delete those rows.
I am not clear, how to get the string from application to SP

Ummm.... this is NOT a bulk load that requires BCP... it's would appear to be coming in as a 2 dimensional parameter... and, yes, I just happen to have an article for you to look over on that...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goodguy SSCertifiable Points: 5456 More actions · Answer 9

Jeff Moden (6/21/2008)
karthikeyan (6/20/2008)
sqldba,
first of all try yourself, otherwise you can't learn anything new. Try to write your own SP. Becuase it is a basic one, without knowing how to wite a simple SP, you can't stand in the market.
So my advice to you is try to write your own, if you face any problem then post it here. It is a good and healthy one.
Not sure if that's right on your part, Karthik... how many times did we tell you to look something up only to have you come right out and say that your didn't want to be told to look it up? How many times did you beg for an example of code?
No... not right on your part...

Spot on, Jeff.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Oh.. i never think my advice turn into very bad one. I don't try to act arrogant(never).

If my words looks like that , then i am sorry.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 11

I never try to blame or cheat anybody. Becuase i don't like that, i always go smoothly with my sorrounding.

I thought i gave my advice without hurting him. But if my part is wrong then 'Really...sorry...'.

http://www.sqlservercentral.com/Forums/Topic520436-8-1.aspx#bm520452

karthik

goodguy SSCertifiable Points: 5456 More actions · Answer 12

Karthik, we can all see that Jeff did not intend to offend you so let us not blow this out of proportion.

Let's call it a truce for the sake of argument.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons