Trigger

Create table test (Id int,Name varchar(50))
Create table tblTest (Id int,testvalue varchar(50),Name varchar(50))
Go
Create TRIGGER tritest /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name FROM INSERTED WHERE inserted.id=tbltest.id
END
Go
insert into tblTest values(2,'TEST','MURLI')

SQL SERVER – Introduction to SERVERPROPERTY and example

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.

Run following script to see all the properties of server.
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

Using MERGE in SQL Server to insert, update and delete at the same time

Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

SYntex :-
MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE] ON <search_condition>
[WHEN MATCHED THEN <merge_matched>]
[WHEN NOT MATCHED [BY TARGET] THEN <merge_not_matched>]
[WHEN NOT MATCHED BY SOURCE THEN <merge_ matched=""> ]

Example:-

--Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO


--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO

List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

2.
Select * from INFORMATION_SCHEMA.CONSTRAINT_Table_Usage


Insert and Update with OpenXML in SQL

This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.

 

declare @i int

 

exec sp_xml_preparedocument @i output, 

'<mydata>

  <test xmlID="3" xmlData="blah blah blah"/>

  <test xmlID="1" xmlData="blah"/>

</mydata>'

 

insert into test 

select xmlID, xmlData 

from OpenXml(@i, 'mydata/test')

with (xmlID int, xmlData nvarchar(30))

where xmlID not in (select xmlID from test)

 

update test

set test.xmlData = ox.xmlData

from OpenXml(@i, 'mydata/test')

with (xmlID int, xmlData nvarchar(30)) ox

where test.xmlID = ox.xmlID

 

exec sp_xml_removedocument @i

 

How to check an array item exist or not in the json array based on the item property value in javascript

In my current project i am working more with jquery and json.In this project i had a requirement to find the json object exist or not in the json array based on the item property value.I had googled a bit and did not find the answer.

Then i wrote a prototype method to find the json object exist or not.

Array.prototype.containsJsonObj = function(name,value) {
var isExists=false;
$.each(this,function(){
   if(this[name]==value){
   isExists=true;
   return false;
}});
return isExists;
};

Below is the json array

var jsonArray=[{"firstName":"John","lastName":"Doe","age": 23 },
            { "firstName":"Mary","lastName" :"Smith","age": 32 }]

we can check whether the array item exist or not as shown below

if(jsonArray.containsJsonObj("firstName","John"))
{
   //if it exists enter into this loop
}
else
{
    //if it does not exist enter into this loop
}

How to get checkboxlist selected item values on client side using javascript

Sometimes, you may need to find out server side checkboxlist selected item values on client side.By default checkboxlist does not send item value to the client.This problem can be solved by assigning values to each item of checkboxlist in page load event as shown below

protected void Page_Load(object sender, EventArgs e)
{
  foreach (ListItem li in cblListItems.Items)
  li.Attributes.Add("mainValue", li.Value);
}

Then call the below javascript function on click of checkboxlist.

function GetCheckBoxListValues(chkBoxID)
{
var chkBox = document.getElementById(chkBoxID);
var options = chkBox.getElementsByTagName('input');
var listOfSpans = chkBox.getElementsByTagName('span');
for (var i = 0; i < options.length; i++)
{
if(options[i].checked)
 {
  alert(listOfSpans[i].attributes["mainvalue"].value);
 }
}
}