February 2, 2012 at 7:13 am
I'm newbie to SQL Server, mostly with Oracle background. This is what I'm trying to accomplish
1)Select deptno from dept table
2)Select all customers for each dept and process each emp row.
3)loop through to the next dept and do step 2 untill the end of row in dept.
I have used for cursor loops in Oracle, which is the recommended efficient way for loops and processing rows.
Is there something similar in SQL Server 2008 to do the above processing.
Appreciate inpout on this.
February 2, 2012 at 7:17 am
Your requirement is not clear. May be you should provide table structure with sample data and the output you needed.
It seems like you are expecting others to write your query.
--- Babu
February 2, 2012 at 7:38 am
svakka (2/2/2012)
I'm newbie to SQL Server, mostly with Oracle background. This is what I'm trying to accomplish1)Select deptno from dept table
2)Select all customers for each dept and process each emp row.
3)loop through to the next dept and do step 2 untill the end of row in dept.
I have used for cursor loops in Oracle, which is the recommended efficient way for loops and processing rows.
Is there something similar in SQL Server 2008 to do the above processing.
Appreciate inpout on this.
Let's get this straight right away:
cursors are not the recommended effecient way in SQL Server 🙂
They process data row by row instead of a set-based fashion, so they are inherently slow.
Before someone here at this forum can help you write a query, you need to give us more information. Preferably table schema's, sample input and desired output. See the links in my signature on how to ask questions.
Most importantly: what do you mean with "process emp row"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 8:40 am
Svakka,
I worked with Oracle and for SQL Server most cursor based "traditional ways" can be done using relational logic.
Tell more about your goal.
Its a insert, update, delete?
Can you join dept and customers tables?
February 2, 2012 at 12:34 pm
Thanks everyone.
I can't post the data as it is sensitive therefore I was trying to take Employee Dept example. Since, I'm new to SQLServer and this forum,I was hoping an insight into how to solve this. Here is a sample data which will hopefully simulate the situation
Problem Statement : For each row in Table A; textname will be a parameter to Table B select statement and should return all the rows for ABC (example) Plus all the rows which are not 'ABC' and Ind= 1.
Table A
-----
TextName
ABC
DEF
GHI
Table B
----------
Id Ind TextName
101 1 ABC
102 1 ABC
103 2 ABC
104 2 ABC
105 1 DEF
105 1 DEF
106 2 DEF
107 1 GHI
108 2 GHI
Output
Id Ind TextName MastText
101 1 ABC ABC
102 1 ABC ABC
103 2 ABC ABC
104 2 ABC ABC
105 1 DEF ABC
105 1 DEF ABC
107 1 GHI ABC
101 1 ABC DEF
102 1 ABC DEF
105 1 DEF DEF
105 1 DEF DEF
106 2 DEF DEF
107 1 GHI DEF
101 1 ABC GHI
102 1 ABC GHI
107 1 GHI GHI
108 2 GHI GHI
February 2, 2012 at 12:43 pm
I can't post the data as it is sensitive therefore I was trying to take Employee Dept example.
That's no problem. Just make up some sample data.
Problem Statement : For each row in Table A; textname will be a parameter to Table B select statement and should return all the rows for ABC (example) Plus all the rows which are not 'ABC' and Ind= 1.
I don't understand this at all. What does textname will be a parameter to Table B mean?
So in order to truly being to have a chance at helping you, you are going to have to do a little work up front.
Please provide ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. In addition a clear explanation of what you are trying to accomplish will help other understand your problem so we can provide something useful.
Please read the first link in my signature for best practices on posting this type of information.
_______________________________________________________________
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/
February 2, 2012 at 12:48 pm
Like it?
create table TableA (textname char(3))
GO
insert into tableA values ('ABC'),('DEF'),('GHI')
GO
create table TableB (id int, ind tinyint, textname char(3))
GO
insert into tableB values
(101,1,'ABC')
,(102,1,'ABC')
,(103,2,'ABC')
,(104,2,'ABC')
,(105,1,'DEF')
,(105,1,'DEF')
,(106,2,'DEF')
,(107,1,'GHI')
,(108,2,'GHI')
GO
select *
from tableA a
cross join TableB b
where (a.textname = b.textname) or (a.textname <> b.textname and b.ind = 1)
GO
drop table tableA
drop table tableB
GO
February 2, 2012 at 12:49 pm
Here is an example of what I mean (based on your previous post)
create table #TableA
(
TextName char(3)
)
create table #TableB
(
ID int,
Ind int,
TextName char(3)
)
insert #TableA
select 'ABC'
union all
select 'DEF'
union all
select 'GHI'
insert #TableB
select 101, 1, 'ABC'
union all
select 102, 1, 'ABC'
union all
select 103, 2, 'ABC'
union all
select 104, 2, 'ABC'
union all
select 105, 1, 'DEF'
union all
select 105, 1, 'DEF'
union all
select 106, 2, 'DEF'
union all
select 107, 1, 'GHI'
union all
select 108, 2, 'GHI'
I don't quite understand #TableA since the data is also in #TableB but we can figure that out as we go.
_______________________________________________________________
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/
February 2, 2012 at 12:54 pm
I don't understand the requirements either. However, I would expect the SQL Server solution to be very similar to the Oracle solution. It may help if you were to post an Oracle solution for this problem, and any SQL Server / Oracle inconsistencies could then be investigated.
Dave
February 2, 2012 at 12:57 pm
This is what sample data should look like:
USE tempdb
CREATE TABLE TableA (textName CHAR(3))
INSERT INTO TableA
SELECT 'ABC'
UNION ALL
SELECT 'DEF'
UNION ALL
SELECT 'GHI'
CREATE TABLE TableB (Id int, ind int, textname char(3))
INSERT INTO TableB
SELECT 101, 1, 'ABC'
UNION ALL
SELECT 102, 1, 'ABC'
UNION ALL
SELECT 103, 2, 'ABC'
UNION ALL
SELECT 104, 2, 'ABC'
UNION ALL
SELECT 105, 1, 'DEF'
UNION ALL
SELECT 105, 1, 'DEF'
UNION ALL
SELECT 106, 2, 'DEF'
UNION ALL
SELECT 107, 1, 'GHI'
UNION ALL
SELECT 108, 2, 'GHI'
SELECT b.id, b.ind, b.textName, a.textName
FROM TableA a
CROSS JOIN TableB b
DROP TABLE TableA
DROP TABLE TableB
I also included a possible start to a solution.
Jared
CE - Microsoft
February 2, 2012 at 1:43 pm
Thank you so much jcb. This is exactly what I need. I'm curious on how the performance is for cross joins
February 2, 2012 at 1:49 pm
svakka (2/2/2012)
Thank you so much jcb. This is exactly what I need. I'm curious on how the performance is for cross joins
The join itself it so much an issue on performance as what you are doing in a cross join. It is a cartesian of the two tables so the amount of data returned grows exponentially as the rowcount increases. It is fast on this little dataset but as the tables get bigger...well it is going to return a mountain of rows as the tables get larger.
_______________________________________________________________
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/
February 2, 2012 at 2:21 pm
Thanks Sean. What will be an alternative solution
February 2, 2012 at 2:34 pm
Given what you need for output that is about all you can do. I just wanted to make sure you understand how much data can be pulled back from a cross join. In your case it is what you want returned.
_______________________________________________________________
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/
February 2, 2012 at 3:01 pm
Sean Lange (2/2/2012)
Given what you need for output that is about all you can do. I just wanted to make sure you understand how much data can be pulled back from a cross join. In your case it is what you want returned.
What do you think about an INNER JOIN TableB ON (a.TextName = b.TextName) OR (a.textName <> b.TextName AND b.ind = 1)?
SELECT b.id, b.ind, b.textName, a.textName
FROM TableA a
INNER JOIN TableB b
ON (a.TextName = b.TextName) OR (a.textName <> b.TextName AND b.ind = 1)
I can't see a difference with no indexes and this small data set... Even in actual execution plan. However, that may change when indexes are introduced and data gets much larger?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply