mstevens92,

To a certain extent, designing applications and databases is an art which requires experience. In addition to the above advice I would strongly suggest you join local .Net and SQL...

You have to use all the column names.

In management studio, if you right click on the table name, select 'Script Table as' and 'Update', then the column names will be...

If you want the most recent version for a given bp_id try the ROW_NUMBER() approach.

As you have not given test data, I do not know which table bp_id is in.

(Add...

This is how update from works - it can cause obscure bugs in production code.

You have to make sure that only one row is joined, something like:

UPDATE R

SET prm_proc =...

You are more likely to get a reply if you provide test data and expected results.

I, for one, do not understand what you are trying to do.

You could also look at using ASP caching:

http://msdn.microsoft.com/en-us/library/ms178604.aspx

etc

There are parallelism bugs with scope_identity():

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

so use OUTPUT.

No - the exists checks that...

You probably have duplicates in the SELECT statement.

As you have not given us the PK, the best I could suggest is to try SELECT DISTINCT...

INSERT INTO FTEST.dbo.FA00100(<column_list>)

SELECT

D2.assetindex + D1.RowNum

,<Other_Columns>

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY 1) AS RowNum

,<Other_Columns>

FROM INFOR.TRAIN.IMSV7.COMPVEH

LEFT JOIN FA00100

ON COMPVEH.UNITID = FA00100.ASSETID

WHERE FA00100.ASSETID IS NULL

) D1

CROSS JOIN

(

SELECT MAX(assetindex) AS assetindex

FROM FTEST.dbo.FA00100 WITH (UPDLOCK)

) D2

The following blog does a reasonable job of explaining the potential problems of JOINs in the UPDATE statement.

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

Maybe:

WHERE ld.delq_dt >= DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) -1, 0)

AND ld.delq_dt < DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP), 0)

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...