SQL Query Tasks

Random Number Generation
Convert Datatype
Replace String
Numeric value Retrieval

1.Generate random number in the SQL Table
ROW_NUMBER() OVER (ORDER BY EmployeeName) AS Row
------------------
2.SQL Column convert from varchar value into Integer value

CAST([Value] AS INT) AS PeerValue
--------------------------

3.SQL Column convert from int value into varchar value
CAST(Prset AS varchar(10)) AS FinalValue
------------------
4. Replace SQL Column contains "Comma" character to Null Values
CAST(Replace([Value],',','') AS FLOAT) AS PrsetValue
5. SQL Column retrieve only numeric Value, It will retrict Alphabets,special Characters
[Value] not like '%[^0-9]%'

Table Column value retrieved using Multiple Joins

select p.Hotel as [Pro],l1.LookupText AS [Met1],l2.LookupText AS [Met2],l3.LookupText AS Met3,[Value],[Year] from [Pro] p INNER JOIN Procs_Seg_EXL E on
p.PropID=E.PropID
INNER JOIN [LKUP] l1 on l1.lupid=E.Me1
INNER JOIN [LKUP] l2 on l2.lupid=E.Me2
INNER JOIN [LKUP] l3 on l3.lupid=E.Me3

Create Temp Tables in SQL Server

Create PROCEDURE [dbo].[GetTempData]
(
   @RegionID varchar(max) = 0
)
AS
CREATE TABLE #tbl_2
(
PropertyID VARCHAR(250),
FTValue int,
FinalValue VARCHAR(250)
)
CREATE TABLE #tbl_3
(
PropertyID VARCHAR(250),
FTValue int,
FinalValue VARCHAR(250)
)

Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('141',5000,'1/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('142',4000,'2/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('143',4000,'3/4')
Insert Into #tbl_2 (PropertyID,FTValue,FinalValue) Values('144',3000,'4/4')

INSERT #tbl_3 SELECT PropertyID,FTValue,FinalValue from #tbl_2

select * from #tbl_3
DROP TABLE #tbl_2
DROP TABLE #tbl_3
END

Move Sharepoint Content Database from SQl server 2005 to SQl server 2008 R2

Attach and Detach SQL server database 2005 to 2008 R2

It is going on

Opening WebPart Maintenance Page

Shortcut to open Instead of going to the Edit Properties form of the page, we can easily open the WebPart Maitenance page by just adding the following query string to the page URL
?contents=1
 So, if your page URL is 'http://kar/pages/default.aspx' then after appending the query string it should look like ' http://rams/pages/default.aspx?contents=1'

Using this approach, we can open the webpart maintenance page for all the forms pages (like /pages/forms/allitems.aspx?contents=1).

But its not safe to remove or add any webparts in these pages, as they may effect the normal funcitonality.

This approach works on both MOSS 2007 and SPS 2010.

Copy from One database table data to anoother database table data MS SQL Query

insert into StarDB.dbo.Associate1 select * from StarDB_Production.dbo.Associate1