LEFT JOIN to a derived table of the current max per match column:

INSERT INTO Table1

  (Sequence, OtherColumns)

SELECT 

   ISNULL( dt.CurrentSeq, 0) + 1, OtherColumns

FROM Table2

LEFT JOIN

(

  SELECT MatchColumn, Max(Sequence) As CurrentSeq

 ...

>>If @BU is an Array then use the IN operator.

You can't use a variable containing a comma-separated list directly with IN().

You either need to

- Construct dynamic SQL

- Parse the list into...

>>Well, the result cannot be more than 40,000, no matter how many users has how many records.

How do you know ?

You need to understand how joins work if there...

Depends on what you want to see - you get large resultsets if a person has 3, 4 or more records.

This is an alternative that shows only users with...

>>Why would this be?

Check the execution plan in each case (highlight the SQL and hit CTRL-L in query analyser).

Something is causing the optimiser not to use the index. This occurs...

SELECT u1.fname, u1.lname, u1.username, u2.fname, u2.lname, u2.username

FROM Users As u1

INNER JOIN Users As u2

  On (u1.fname = u2.fname AND

        u1.lname = u2.lname )

WHERE u1.username <> u2.username

Pre-compute the required selection date into a variable and use that. Should allow optimizer to use the index:

Declare @DateFrom As SmallDateTime

Select @DateFrom = Convert(Smalldatetime, Convert(int,DATEADD(day, -7, getdate())))

Select * from table...

Using functions prevents index usage. That's why you're seeing the problem.

>>We have an ongoing debate here at my company on whether or not to include an environment specific identifier in the database name or not.

I'd say it depends on the...

>>The weight always ends in PT and is always before the UPC.

Is there ever anything after the UPC ? Is the UPC always a number with no spaces ?

>>SELECT CHARINDEX('PT ', @Msg) -- this gets you the position of the PT

Careful

Product Name= 'ICE CREAM FOR THE INEPT STRING PARSER FUNCTION 55PT...

>>has same employee id, name but have different department names or different pay rate or addresses.

And that's why I asked my original question. If you can't come up with business rules...

>>I am assuming based on the name of the target server

It is not a "target server". It is simple 3-part naming of a table - "Intranet" in this case...

There is no need for a sub-query:

SELECT s.strMajorRoad, COUNT(s.idSCOOTChecklist) AS COUNT

FROM tblScootChecklist As s

INNER JOIN AGSSITE As agc

  On (agc.ID = s.intIntersection)

WHERE CharIndex( s.strMajorRoad, agc.Descriptor ) > 0

GROUP BY s.strMajorRoad

>>Can you explain why I am returning some rows twice?

Without the DDL including PKey and Unique constraint defs, and sample data, probably not.

If you get more rows that expected, it...