November 28, 2011 at 4:46 pm
i have a query that gives a result set
i am doing an insert of that result into a temp table #t1
table #t has 5 columns namely
col1 col2 col3 col 4 col 5
now i am doing a count on this temp table
eg select col1 count(col2) from #t1 group by col1 , col2
having count(col2) >1
i am getting some 100 records with count(col2) >1
how can i stop those 100 records while i am doing an insert operation
from the result set to temp table #t1
November 28, 2011 at 5:00 pm
I am sorry, but your statement of the problem is very unclear. I (and many others) are very happy to help, but we need a better description from you.
Please restate the question using the actual table and column names. If possible, give us some actual code snippets. I think you will find it much easier to describe the problem in detail if you do. 🙂
November 28, 2011 at 5:26 pm
this is what i am doing
insert into #t1(col1, col2, col3, col4, col5)
select col1 col2 col3 col 4 col5 from R1
where
R1.col1 > 0
and R1.col5 = 1
after #t1 is populated
i did a count for col2 in the following way
select col1 count(col2) from #t1 group by col1 , col2
having count(col2) >1
i am gettin 100 records for the count i did for col2
now i do not want to limit those records at the insert level itself
so
i tried in the following way
this is what i am doing
insert into #t1(col1, col2, col3, col4, col5)
select col1 col2 col3 col 4 col5 from R1
where
R1.col1 > 0
and R1.col5 = 1
and R1.col2 not in
(select col1 count(col2) from R1 group by col1 , col2
having count(col2) >1)
and it is throwing an error
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
November 28, 2011 at 5:41 pm
You want it to look like:
insert into #t1(col1, col2, col3, col4, col5)
Select
col1, col2, col3, col4, col5
from
R1
where
R1.col1 > 0
and R1.col5 = 1
and R1.col1 not in
(select col1 from R1 group by col1
WHERE Col1 > 0 AND col5 = 1
having count(col2) > 1)
If that's not what you're looking for, please see the first link in my signature, it will help you create sample data and sample results so we can define the problem exactly and give you explicit assistance.
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
November 28, 2011 at 6:03 pm
😎
Evil Kraig F (11/28/2011)
You want it to look like:
insert into #t1(col1, col2, col3, col4, col5)
Select
col1, col2, col3, col4, col5
from
R1
where
R1.col1 > 0
and R1.col5 = 1
and R1.col1 not in
(select col1 from R1 group by col1
WHERE Col1 > 0 AND col5 = 1
having count(col2) > 1)
If that's not what you're looking for, please see the first link in my signature, it will help you create sample data and sample results so we can define the problem exactly and give you explicit assistance.
Craig,
That is a excellent analysis without the DDL & Sample Data.
I'm impressed!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply