April 10, 2009 at 12:06 am
Hi,
I have following requirement.
I have to transfer data from Source DB X tables 1,2,3,4,5 to destination DB Y A,B,C,D,E for SIX tables.
Case1:My First step is I have to check a column in destination table D.d whether it is null or not. If it's null (which is always a possibale case for the first run) we have to insert a value into it.
And start our insertion process.
Case 2: If column D.d is not null(Which is always a possible case when we run the code second time) , we have to check whether Min(2.a)>D.d( meaning if a value exhist in D.d we have to do a compersion before inserting records.
If this conditions satisfies then we have to insert records fron source to destination else process stops.
Following is my Code :
DECLARE @testLog smallint
SELECT DISTINCT @RunLog=MAX(log value ) FROM Destination table
IF @testLog IS NULL
-- Inserting value in to test log
BEGIN
INSERT INTO Table A (column 1,Column2)
SELECT TOP 1 column1,MIN(column2) FROM table from source
WHERE ---
GROUP BY column2
END
? Comparision (has to occurs only when testlog is not null)
ELSE IF (SELECT MIN(column2) FROM 3) >(Table A.column2)
? Insering
BEGIN
--first part
INSERT INTO B
SELECT * FROM 2
-- Part two
INSERT INTO C SELECT * FROM 3
END
My requirement is:
If test log in Null, I have to insert a value to it and then start my insertion process with out any comparison.
If test log is not null, I have to do a comparison between a tables. Column in source and destination. If the condition satisfies Insertion process has to run else process has to stop with out any insertion.
Problem:
But in my code, the process is going to Comparison step even the test log value is null.
Any help on this is really appreciated.
April 10, 2009 at 7:12 am
If you would please write a short script to define temporary tables and put a few rows into them, then people can test their solutions to your problem before posting coded replies. You might take a moment to look at the article here[/url]. Following the examples for posting will not only get you tested answers faster, it will encourage more people to tackle your problem, and win you friends among the volunteers who are helping you.
Thanks 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 10, 2009 at 7:24 pm
Why not try GOTO Step#. You can find it on BOL. I had to write something recently and it worked like a charm.
Example
If A = B
BEGIN
GOTO Step1
END
ELSE
BEGIN
GOTO Step2
END
Step1:
GOTO Step3
Step2:
GOTO Step3
Step3:
April 11, 2009 at 3:31 am
Shane Redding (4/10/2009)
Why not try GOTO Step#. You can find it on BOL. I had to write something recently and it worked like a charm.
Hi
The GOTO should always be one of the last approaches. Here we call this "spaghetti" code because it breaks the sequential work of code:
GOTO A
B:
PRINT 'B'
GOTO FINISH
A:
PRINT 'A'
GOTO B
FINISH:
PRINT 'Done'
Usually try to handle context sensitive code execution by flags or IF-clauses. The only common GOTO (or rather goto) I know is CLEANUP in C and C++ to step out of methods and do some cleanup work.
I completely confirm Bob. Please provide some sample data to clarify a bit more the business case, please. I would like to help but I don't know how :-).
Greets
Flo
April 11, 2009 at 7:50 am
In regards to the below, perhaps you should go work for the microsoft sql server team as GOTO is not uncommon in TSQL. 🙂
"The GOTO should always be one of the last approaches. Here we call this "spaghetti" code because it breaks the sequential work of code:
Usually try to handle context sensitive code execution by flags or IF-clauses. The only common GOTO (or rather goto) I know is CLEANUP in C and C++ to step out of methods and do some cleanup work."
April 11, 2009 at 8:06 am
GOTO is not uncommon in TSQL
Really? It's been so long since I've seen one, that I'd forgotten that it ever existed.
I'm with Flo. GOTOs can lead to spaghetti code. Good structure is always preferable.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 8:20 am
I hope that I would never use GOTO in SQL code.
If you have to use that kind of logic then you might want to use stored procedures and use something like:
If A = B
BEGIN
EXEC Stored_Procedure_1
END
ELSE
BEGIN
EXEC Stored_Procedure_2
END
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 11, 2009 at 8:24 am
The original example was actually following the IF/Begin-End ELSE/Begin-End structure.
By the way, Alvin, enjoyed the presentation Thursday night.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 8:28 am
Bob Hovious (4/11/2009)
The original example was actually following the IF/Begin-End ELSE/Begin-End structure.By the way, Alvin, enjoyed the presentation Thursday night.
Thanks Bob. FYI, you're doing the presentation next month. 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 11, 2009 at 8:39 am
:w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 8:59 am
Shane Redding (4/11/2009)
In regards to the below, perhaps you should go work for the microsoft sql server team as GOTO is not uncommon in TSQL. 🙂
Good heavens, what do you base that claim on?
You should NEVER use GOTO because you should NEVER need to. One of the first goals of the last 35 years of software language technology development has been to make GOTO entirely unecessary.
Are they really not teaching this in college anymore?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2009 at 9:38 am
Shane Redding (4/11/2009)
In regards to the below, perhaps you should go work for the microsoft sql server team as GOTO is not uncommon in TSQL. 🙂
Just because the MS people do it doesn't make it a universally good idea. Witness the design of MSDB or some of the code that management studio generates for a number of examples of that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 9:41 am
Alvin Ramard (4/11/2009)
If you have to use that kind of logic then you might want to use stored procedures and use something like:If A = B
BEGIN
EXEC Stored_Procedure_1
END
ELSE
BEGIN
EXEC Stored_Procedure_2
END
That's often the best approach for conditional SQL queries, due to some rather unamusing parameter sniffing problems that multiple if-else constructs with queries inside can cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 10:05 am
GilaMonster (4/11/2009)
Alvin Ramard (4/11/2009)
If you have to use that kind of logic then you might want to use stored procedures and use something like:If A = B
BEGIN
EXEC Stored_Procedure_1
END
ELSE
BEGIN
EXEC Stored_Procedure_2
END
That's often the best approach for conditional SQL queries, due to some rather unamusing parameter sniffing problems that multiple if-else constructs with queries inside can cause.
Gail approves. I feel smarter now. 😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 11, 2009 at 10:07 am
You should feel safer now, too. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply