I'd try instead to just:

(1) just in case, as a backup, copy existing key column(s) and data column to be changed to a keyed backup table

(2) verify that existing data...

You don't need to re-count the rows every time.

DECLARE @BatchSize int

DECLARE @RowRount int

DECLARE @TableRowCount int

DECLARE @TableRowLimit int

SET @BatchSize = 10000

SELECT @TableRowCount = COUNT(*)

FROM PtActs WITH (NOLOCK)

SET @TableRowLimit = 10050000

WHILE...

The VALUES clause simplifies it considerately, which can be great for longer lists:

CROSS APPLY (

SELECT MIN(date)

FROM (

...

SQLSACT (1/9/2015)

---

SELECT sku, product_description,

(SELECT plant_nbr

FROM ProductPlants AS B

WHERE B.sku =...

DECLARE @sql varchar(max)

DECLARE @column_list varchar(max) --as returned by first query

SET @column_list = 'age,sex,race' --for example

SET @sql = 'UPDATE tbl2 SET col2 = [' + REPLACE(@column_list, ',', ']+[') + ']'

PRINT...

I suspect recursion would be great for this, but that's not my specialty. This is somewhat akin to the earlier LOJ version, which unfortunately I didn't see until now...

Eric M Russell (1/9/2015)

---

ScottPletcher (1/8/2015)

---

You could also GROUP directly on the CASE expression itself:

SELECT

CASE

WHEN message_text LIKE...

Not necessarily true -- a given index can be empty even if the table is...

You could also GROUP directly on the CASE expression itself:

SELECT

CASE

WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'

...

Might as well have max flexibility on the number of name levels provided:

DECLARE @tablename varchar(500)

DECLARE @sql varchar(8000)

SET @tablename = 'YourTableName'

--SET @tablename = 'server1.db1..YourTableName'

SET @sql = 'SELECT * FROM '...

SELECT

DD1.[Incident_x0020_Date] AS [Incident Date],

1 AS [Order],

DD1.[Int_x002f_Dom] AS [Location],

DD1.[ACTION] AS [Action],

DD1.[Channel] AS [Channel Type],

SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption' THEN 1 ELSE 0 END)...

Note that you can also create non-temp tables in the tempdb database. That way the table will be there even if the connection that created it "gets broken" or...

The index is empty, i.e., there are no rows in that index.

SELECT sm.*, tv_lookup.Amount AS tv_Amount, tv_lookup.PercRate AS tv_PercRate, tv_lookup.Code AS tv_Code

FROM #SM sm

OUTER APPLY (

SELECT TOP (1) *

FROM #TestValues tv

...

Jeff Moden (1/8/2015)

---

Now, based on what I just said, one of my first questions on an SQL Server technical interview for DBAs and Developers alike is "How do you get...