How to Import the Excel Data into the MS SQL Database table

Create a Dummy table name is Dummy_Tyring and create column name as per Excel Sheet.
The existing table name is Properties

steps:
1.Just copy all data from Excel File and paste into the Dummy_Tyring table

2.Execute the below update Query
update [Properties]
set [Properties].Tiring = Dummy_Tyring.Tiring
FROM [Properties]
INNER JOIN Dummy_Tyring ON [Properties].PropertyPIN = Dummy_Tyring.PIN

3.Now all the dummy data information has moved to Existing Database table.

 

How to check the W2WP Process is running in the Sharepoint

Opend cmd

C:\Windows\System32>issapps.vbs

ASP.Net and C Sharp String Manipulation

1.using foreach loop for split the comma in that string

 foreach (string c in Controls.Text.Split(','))
                    {
                        if (c == Target.ID)
                        {
}
}


2.Get the specific values in that string using substring method


3.Replace the string
toAddress = toAddress.Replace(";", ",");


4.Get the string after comma
string[] words = subject.Split(',');
                string title = words[0];
                string Roomtoretrieve = words[1];
                string MeetingRoom = words[2];

Update same table column using Join

UPDATE t2
SET t2.FinalValue = t3.FinalValue ,t2.Updated='true'--t3.FinalValue
from #tbl_3 t2 INNER JOIN #tbl_3 t3 ON t3.FTValue=t2.FTValue
WHERE t2.Updated='false'

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