July 26, 2011 at 3:51 am
Hi need Help to solve Below Query ..
i have a select statment which returns one or more than one record
if it returns more than one record i need to insert into another table else i need to stop insert
BEGIN
CREATE TABLE #TempA
(
SOFTWRARE_ID varchar(12),
LOGIN_ID varchar(50),
)
CREATE TABLE #TempB
(
SOFTWRARE_ID varchar(12),
LOGIN_ID varchar(50),
)
INSERT INTO #TempA
values('A','Login1')
--INSERT INTO #TempA
--values('A','Login2')
--INSERT INTO #TempA
--values('A','Login3')
insert into #TempB
select SOFTWRARE_ID,LOGIN_ID from #TempA
where COUNT(LOGIN_ID)>1
drop table #TempA
drop table #TempB
END
July 26, 2011 at 4:27 am
insert into #TempB
select SOFTWRARE_ID,LOGIN_ID from #TempA
GROUP BY SOFTWRARE_ID
HAVING COUNT(LOGIN_ID)>1
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
July 26, 2011 at 6:10 am
sorry.
With above reply it Giving following Error Message
Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
July 26, 2011 at 6:15 am
ramineni.suresh661 (7/26/2011)
sorry.With above reply it Giving following Error Message
Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What's unclear about that? Put that column in the group by and it'll work!
July 26, 2011 at 6:26 am
Ninja's_RGR'us (7/26/2011)
ramineni.suresh661 (7/26/2011)
sorry.With above reply it Giving following Error Message
Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What's unclear about that? Put that column in the group by and it'll work!
No it won't, because he wants a count by software and to insert the details if there are more than one login.
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
July 26, 2011 at 6:41 am
Ok, but you'll leave him waiting like that for the real answer? 😉
July 26, 2011 at 6:43 am
Only while I load up Management Studio, write and test it.
INSERT INTO #TempB (SOFTWRARE_ID, LOGIN_ID)
SELECT SOFTWRARE_ID, LOGIN_ID
FROM
(SELECT SOFTWRARE_ID,LOGIN_ID, COUNT(*) OVER (PARTITION BY SOFTWRARE_ID) AS LoginCount FROM #TempA)sub
WHERE LoginCount>1
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply