Please help on Logic

Question

Post reply

Please help on Logic

Viewing 8 posts - 16 through 22 (of 22 total)

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

xRafo SSChasing Mays Points: 623 More actions · Answer 1

if the temporary table is in dynamic sql?

____________________________________________________________________________
Rafo*

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

xRafo (2/9/2012)
if the temporary table is in dynamic sql?

So what? You can use temp # tables in dynamic sql.

What is you point?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

A) Dynamic SQL should be used sparingly and with a specific intent to optimization, otherwise: kill it with fire. Even then, # tmps work just as well in dynamic as table variables. Table Variables are primarily good for two things: Table Parameters and Functions. Neither of those are safe to use on large systems if you don't know how badly table variables affect estimated execution plans.

B) Best solution to this is usually an SSIS package, because you redirect error rows to another 'error table' that's more generic and you can review the result set. It also splits off the stream without having to do any heavy lifting in code or table manipulation. It also avoids you having to type check every little thing about every single column. Any error, off to the dump table!

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

xRafo (2/9/2012)
Hmm, can u prove it?, i mean,
do you have an example and probe it what you say?

It's a well known fact. Google it. 😉

--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: 1004356 More actions · Answer 5

Actually, this is all very easy using BULK INSERT which now allows you to do like we used to do in BCP. Sequester errors in a file and keep on truckin'! Just set the max number of errors to some ridiculously high number and any data that doesn't "fit" the target table will go to the error file you identify. NO special handling required. 😉 And, look Ma! No SSIS validation required. :w00t:

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Jeff Moden (2/13/2012)
Actually, this is all very easy using BULK INSERT which now allows you to do like we used to do in BCP. Sequester errors in a file and keep on truckin'! Just set the max number of errors to some ridiculously high number and any data that doesn't "fit" the target table will go to the error file you identify. NO special handling required. 😉 And, look Ma! No SSIS validation required. :w00t:

LOL, nice Jeff. I'll have to check that out. How does BCP/Bulk Insert inform you when a process includes items to an error file? Difficult to detect/report on to a DBA from an automated job?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

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

Evil Kraig F (2/16/2012)
Jeff Moden (2/13/2012)
Actually, this is all very easy using BULK INSERT which now allows you to do like we used to do in BCP. Sequester errors in a file and keep on truckin'! Just set the max number of errors to some ridiculously high number and any data that doesn't "fit" the target table will go to the error file you identify. NO special handling required. 😉 And, look Ma! No SSIS validation required. :w00t:
LOL, nice Jeff. I'll have to check that out. How does BCP/Bulk Insert inform you when a process includes items to an error file? Difficult to detect/report on to a DBA from an automated job?

It's like any other validation procedure. If the codes checks the error file at the end of the run and it's not empty, then you have errors. 😀

--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)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Ram...total sidebar

but I do think we ought to combine our avatars 😀

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day