This should be quicker:

;WITH cte

AS

(

SELECT a

,COALESCE(MAX(CASE WHEN x = 1 THEN y END), 0) AS b

,COALESCE(MAX(CASE WHEN x = 2 THEN y END), 0) AS c

FROM @t2

GROUP BY a

)

UPDATE T

SET...

Use a number/tally table and CASE. Something like:

INSERT INTO AllocationTable(SourceTableID, AllocatedAmount)

SELECT [ID]

,CASE

WHEN N.Number * 1000 <= S.Amount

THEN N.Number * 1000

ELSE S.Amount % ((N.Number - 1) * 1000)

END

FROM SourceTable S

JOIN master.dbo.spt_values...

I have coped with this sort of rubbish in the past by using a temp table and identity insert:

DECLARE @MaxID int

SELECT @MaxID = MAX([ID])

FROM Parent

SELECT IDENTITY(int) AS [ID]

,*

INTO #temp

FROM #TmpOther

SET...

Bob is suggesting something like:

INSERT INTO phpbb_posts(forum_id, topic_id, post_subject, post_text)

SELECT F2.forum_id, T.topic_id, R.[subject], R.body

FROM replies R

JOIN phpbb_forums F1

ON R.category = F1.Forum_name

AND forum_type = 0

JOIN phpbb_forums F2

ON F1.forum_id = F2.parent_id

AND R.subcategory...

With the limited information provided, the best I could suggest for SQL200 would be:

SELECT *

INTO #Inserted

FROM inserted

WHERE fieldname = 'Test'

IF @@ROWCOUNT > 0

BEGIN

-- Use #Inserted instead of inserted

END

If you post...

It may be worth using a number/tally table:

SELECT TutorID

,ClassDate

,COUNT(DISTINCT N.Number)/60.0 AS TotalTuition

FROM

(

SELECT TutorID

,StartTime

,EndTime

,DATEADD(d, DATEDIFF(d, 0, StartTime), 0) AS ClassDate

FROM #Classes

) D

JOIN dbo.spt_values N

ON N.Number >= DATEDIFF(minute, ClassDate, D.StartTime)

AND N.Number <...

Or use a calculated column:

ALTER TABLE new_call

ADD CalcStatus AS

CASE

WHEN DATEDIFF(d, [Date Quoted], CURRENT_TIMESTAMP) <= 30

THEN CAST('In Progress' AS varchar(11))

ELSE CAST('Expired' AS varchar(11))

END

Either:

DELETE T

FROM TempTable T

WHERE NOT EXISTS

(

SELECT T1.ContentID

FROM TempTable T1

WHERE T.ContentId = T1.ContentId

GROUP BY T1.ContentID

HAVING T.Rank = MAX(T1.Rank)

)

or

DELETE T

FROM TempTable T

LEFT JOIN

(

SELECT T1.ContentId, MAX(T1.Rank) AS Rank

FROM TempTable T1

GROUP BY T1.ContentId

) D

ON...

The idea is to get rid of duplicates and then add in unique negative numbers:

-- get rid of duplicates

UPDATE D

SET ARS_No = 11111

FROM

(

...

As an unique constraint cannot be created with the NOCHECK option, I would be inclined to put some junk data into the ARS_No column of tbl_Person. Assuming that Person_Id is...

Your main problem is the complicated table valued function and the cursor does not help.

Try to make the proc set based. As you have not provided DDL or test data...

From:

http://www.sqlservercentral.com/Forums/Topic758095-338-1.aspx

DECLARE @t TABLE

(

EmpId int NOT NULL

,[Date] smalldatetime NOT NULL

,[Status] varchar(20) NOT NULL

)

INSERT INTO @t

SELECT 1, '20090101', 'Present'

UNION ALL SELECT 1, '20090102', 'Present'

UNION ALL SELECT 1, '20090103', 'Absent'

UNION ALL SELECT 1,...

I suspect using CASE would work better:

DECLARE @StartDate datetime

,@EndDate datetime

SELECT @StartDate = '20090920'

,@EndDate = '20090923'

SELECT [Terminal ID]

,COUNT([Terminal ID]) AS TotalTrans

,COUNT(CASE WHEN Reject = '' AND [Fee Amount] <> '0000000' AND...

In SQL2008 this blog from Hugo Kornelis suggests that using MERGE is the way to go:

http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx

If you have a lot of columns to compare then you can use INTERSECT as...

EXEC sp_executesql ...

Syntax is in BOL.