Sql server tricks

Update table record with a value from another record in same table

UPDATE Ghms_Res_Details

SET 

Request_For=res2.Request_For,

Res_From=res2.Res_From,

Res_To=res2.Res_To,

No_Of_Days=res2.No_Of_Days,

No_Of_Rooms=res2.No_Of_Rooms,

AC_Opt=res2.AC_Opt,

Book_Reason=res2.Book_Reason,

Pay_By=res2.Pay_By,

No_Of_Adults=res2.No_Of_Adults,

No_Of_Childrens=res2.No_Of_Childrens,

Pref_Loc=res2.Pref_Loc,

Pref_GH=res2.Pref_GH,

Alloc_Loc=res2.Alloc_Loc,

Alloc_GH=res2.Alloc_GH,

Book_Remarks=res2.Book_Remarks,

Emp_From=res2.Emp_From,

Emp_Family=res2.Emp_Family,

Emp_Cadre=res2.Emp_Cadre,

Elig_Group=res2.Elig_Group,

Cost_Center=res2.Cost_Center,

Self_App=res2.Self_App,

Guest_Name=res2.Guest_Name,

Guest_Comp=res2.Guest_Comp,

Guest_Type=res2.Guest_Type,

Guest_Natio=res2.Guest_Natio,

Guest_Gender=res2.Guest_Gender,

Guest_Age=res2.Guest_Age,

Med_File_Name=res2.Med_File_Name,

Done_Online=res2.Done_Online,

Base_Res_Code=res2.Base_Res_Code,

Ext_Count=res2.Ext_Count,

L1_Code=res2.L1_Code,

L1_Name=res2.L1_Name,

L1_Email=res2.L1_Email,

L2_Code=res2.L2_Code,

L2_Name=res2.L2_Name,

L2_Email=res2.L2_Email,

App_Rem=res2.App_Rem,

App_Date=res2.App_Date,

Hotel_ID=res2.Hotel_ID,

Confirm_By=res2.Confirm_By,

Confirm_Date=res2.Confirm_Date,

Confirm_Remarks=res2.Confirm_Remarks,

Confirm_Remarks_NA=res2.Request_For,

CheckIn_Date=res2.CheckIn_Date,

CheckOut_Date=res2.CheckOut_Date,

CheckIn_By=res2.CheckIn_By,

CheckIn_Upd_Date=res2.CheckIn_Upd_Date,

CheckOut_By=res2.CheckOut_By,

CheckOut_Upd_Date=res2.CheckOut_Upd_Date,

ECO_Flag=res2.ECO_Flag,

ECO_Remarks=res2.ECO_Remarks,

NoShow_By=res2.NoShow_By,

NoShow_Date=res2.NoShow_Date,

NoShow_Rem=res2.NoShow_Rem,

Cancel_By=res2.Cancel_By,

Cancel_Date=res2.Cancel_Date,

Cancel_Rem=res2.Cancel_Rem,

Feedback_Mail_Flag=res2.Feedback_Mail_Flag,

Trip_ID=res2.Trip_ID,

Yatra_Mail_Flag=res2.Yatra_Mail_Flag,

IsWinHMS=res2.IsWinHMS,

Temp_ID=res2.Temp_ID

FROM Ghms_Res_Details,(select * from Ghms_Res_Details where Res_Code='R454563') as res2

WHERE Ghms_Res_Details.Res_Code='R454562'

How to get only numeric column values?

SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1

Remove space from the Text

SELECT REPLACE(REPLACE(' 11,
sector -2, kharghar, navi mumbai 410210 ', CHAR(13), ''), CHAR(10), '')


Remove time from date

select  CONVERT(date, getdate())

comma separated value

DECLARE @Gate_Desc VARCHAR(8000)
SELECT @Gate_Desc = ISNULL(@Gate_Desc + ',', '') + Gate_Desc
FROM [dbo].[Master_Gate]
ORDER BY Gate_Desc
SELECT @Gate_Desc AS Gate_Desc

Replace Multiple Spaces in String with Single Space

Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')

Cursor
DECLARE @varSrNo AS NUMERIC,
@varResCode AS VARCHAR (15),
@varExist AS INT,
@MainRecord as varchar(15)='null',
@DummyRecord as varchar(15)='null';
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Res_Code,isnull(Temp_Res_Code,'') Temp_Res_Code FROM  Ghms_Res_Details WHERE Temp_ID = 2000003582 AND Res_Status = 0 and Temp_Res_Code is null
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @MainRecord,@DummyRecord
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ID : '+ @MainRecord +', Dummy : '+@DummyRecord
FETCH NEXT FROM @MyCursor
INTO @MainRecord,@DummyRecord
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

SET vs SELECT

SET	SELECT
Can only assign one variable at a time. SET @Index = 1 SET @LoopCount = 10 SET @InitialValue = 5	Can assign values to more than one variable at a time. SELECT @Index = 1, @LoopCount = 10, @InitialValue = 5
When assigning from a query and the query returns no result, SET will assign a NULL value to the variable. DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SET @CustomerID = (SELECT [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC') SELECT @CustomerID -– Returns NULL	When assigning from a query and the query returns no result, SELECT will not make the assignment and therefore not change the value of the variable. DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC' SELECT @CustomerID –- Returns XYZ
When assigning from a query that returns more than one value, SET will fail with an error. SET = (SELECT [CustomerID] FROM [dbo].[Customers]) Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.	When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row. SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] -- No error generated