Wow, thanks for the feedback Lucas.. Hope u understood what the code does 🙂

mister.magoo (1/24/2011)

---

Dave Ballantyne (1/24/2011)

---

Im speaking from a point of pure ignorance about your code, ie i havent run in through, 😉 , but

WHILE @@ROWCOUNT=3

So , you are...

Dave Ballantyne (1/24/2011)

---

Im speaking from a point of pure ignorance about your code, ie i havent run in through, 😉 , but

WHILE @@ROWCOUNT=3

So , you are inserting...

Even i have a doubt in the code , Magoo... Do we really need UNPIVOT ? i guess, your code will work even without the...

How about this:

;WITH RANDOMIZER AS

(

SELECT GroupedCol, MIN(ABS(CHECKSUM(NEWID()))) NWID

FROM #TESTSORTING

GROUP BY GroupedCol

)

SELECT TS.*

FROM #TESTSORTING TS

INNER JOIN RANDOMIZER RND

...

2 options that i could think of:

Option 1 : By using multiple statement , one for INSERT of new records and UPDATE for old records

Option 2 : Put data from...

As u have posted it in 2008 forums, i assume u have SQL 2008 in your machine. This following code will do what u wanted (uses MERGE statement)

--BEGIN TRAN

MERGE Emp_dest...

when u dont have a WHERE clause or JOIN clause , u never are going to use to a INDEX, so ur time taken is quite normal..

Modified version (assumes ID column is already present in the table)

DECLARE @BITBUCKET INT

SET NOCOUNT ON

SET STATISTICS TIME ON

;WITH Unpivoted_List AS

(

SELECT ROW_NUM , ValInCol

FROM

( SELECT ID AS ROW_NUM ,idMvt1,idMvt2,idMvt3 ...

@jeff, u are absolutely right.. i mis-read the requirement; from morning (its morning in INDIA now, as i write) i was thinking about that only.. So i sat and put...

Ok, i had some time to peek into this. Here are the results:

Test Data:

USE TempDB

GO

SET NOCOUNT ON

IF OBJECT_ID(N'MVTS',N'U') IS NOT NULL

DROP TABLE MVTS

CREATE TABLE MVTS ( ...

Sorry for leading you into a wrong path, i figured out a simple UNPIVOT could do what we need. From the requirement, i am guessing that you need to find...

Probably a self-referencing CTE should do it! Have u tried that?

Hey Jeff, thanks for article.. well written and full of information! Thanks a bunch! 🙂

But in any case, David's code will beat mine pants down..