update sort order column in datatable.

If we are sorting based on SorteOrder column in Datatable. many time we need to organize columns based on certain condition following is command to update sort Oder column.

UPDATE Table1
SET SortOrder = vx.RowNum
FROM Table1
CROSS APPLY
(

-- step 1 first execute this comamnd and once you agreed for sortign execute update
SELECT ColumnNameToSetSorOrdre,PrimaryKey, ROW_NUMBER() OVER (ORDER BY ISNULL(ColumnNameToSetSorOrdre, 9999999), ColumnNameToSetSorOrdre)  AS RowNum
FROM Table1
WHERE IsActive = 0
-- AND other conditions
) vx
WHERE vx.PrimaryKey = Table1.PrimaryKey
-- and Other conditions If requried

BR
Mahesh

How to get number of rows in the data table

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString