Also, the format that you're looking for in the CONVERT is 112, which gives you YYYYMMDD without the need to use REPLACE to get rid of extraneous characters.

SELECT Convert(varchar(8), Getdate(),...

Jim-720070 (9/2/2010)

---

Why couldn't I use CTE's? You're kinda right.. I'm actually writing a Crystal report and this is the SQL I'm using in a command. Wish I could use CTE's...

Depending on the number of tables and the types of updates, replication may actually be your best option. SSIS is going to have a hard time dealing with deletes,...

A conditional expression can't appear in your THEN clause. The generic way to fix this is to move it to the WHERE clause with an AND.

WHERE CASE

...

Actually, that's a runtime error. ...

Arjun Sivadasan (8/31/2010)

---

So, as you can see, I need the entire list of records to generate the row number. If I take only a percent of the effective records, my...

I think he's inserting into a temp table, then deleting from the temp table, and then inserting the remaining records into the production table.

If that is the case, you might...

Arjun Sivadasan (8/30/2010)

---

I can't use batch processing as I need the entire set of recordids at one go. This is only a small part of the query and I have...

Brandie Tarvin (8/30/2010)

---

Drew, the code author had no specific transactions within the TRY block. He's treating the TRY as all one transaction. It should be an All or Nothing proposition....

Your pseudo-code doesn't have an explicit transaction defined and you didn't mention whether you had changed your transaction management mode, so I'm assuming that it's using the default which is...

I only suggested SET ROWCOUNT because I...

Actually, I was thinking about this last night, and because of the number of records you may want to do batch the processing. Here's one example. You would...

I tend to use FOR XML to concatenate values together. In some cases, you can also use the quirky update.

Here is the FOR XML version.

if object_id('tempdb..#test') is not null...

If you want to return fields that you don't want to group on, a CTE with Row_Number() is often a good approach.

I changed your Table to a Table variable, but...

SwePeso (8/25/2010)

---

DECLARE@Sample TABLE

(

ID INT NOT NULL

)

INSERT@Sample

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 9 UNION ALL

SELECT11

-- Get the missing...