INSERT in SQL Server

In SQL Server there are many ways to insert records into table. See below:

INSERT row by row:

INSERT INTO Table1 VALUES(1,’Rajesh’,10000);
INSERT INTO Table1 VALUES(2,’Mahesh’,20000);

INSERT records into temp table from existing table:

SELECT * INTO #tempTable 
FROM Table1
--creates new temporary table and inserts data from existing table

INSERT records into new table from existing table:

SELECT * INTO Table2
FROM Table1
--creates new table and inserts data from existing table
--if Table2 already exists it gives error

INSERT records into existing table from another table:

insert into Table2
select * from Table1
--insert records into table from existing table. 
--Throws error if table not exists

--This inserts data from one table to another if both tables exist

INSERT records into existing table from the Stored procedure’s returned result set:

insert into Table2
EXEC Table1_RECORDS
--inserts stored procedure returned records into table if table exists
--No.of Columns in Table2 and no.of Columns return by SP should be same.

SELECT * INTO  #tempTable
EXEC Table1_RECORDS
--Gives Error

MERGE in sql server and its alternatives

MERGE command was introduced in sql server 2008. MERGE command is used to perform INSERT, UPDATE and DELETE operations at once in single command while merging source and target table records. MERGE is useful to merge source table records into target table. It basically does the following.
1) Inserts records into target table if those records are in source table but not in target table.
2) Updates records in target table if those records are modified in source table.
3) Deletes records from target table if those records are deleted from source table
Example:
Source EMPLOYEE1 table and target EMPLOYEE2 table have the below records:

Merge command looks like:

MERGE EMPLOYEE2 AS TARGET
USING EMPLOYEE1 AS SOURCE
ON TARGET.Id = SOURCE.Id
WHEN MATCHED AND TARGET.Name <> SOURCE.Name
THEN 
UPDATE SET TARGET.Name = SOURCE.Name /*updating if ids are same and names are different*/
WHEN NOT MATCHED BY TARGET
THEN 
INSERT (Id,Name)
VALUES (SOURCE.Id,SOURCE.Name) /*inserting if record is not found in target*/
WHEN NOT MATCHED BY SOURCE
THEN DELETE /*deleting record if it is not found in source*/
-- optional lines
OUTPUT $action,
DELETED.Id,
DELETED.Name,
INSERTED.Id,
INSERTED.Name;

Here $action column is of NVARCHAR(10) variable, it uses to display type of operation performed on a particular row of a table like INSERT,DELETE or UPDATE. This is optional one. Remember MERGE statement should end with semicolon (;).

After execution of above statement we will have the following result set and respective source and target tables

But, this MERGE is not available in earlier versions of sql server mainly in sql server 2005. One can achieve this functionality by writing queries like.

UPDATE EMPLOYEE2 SET Name=E1.Name FROM EMPLOYEE2 E2
INNER JOIN EMPLOYEE1 E1 ON E2.Id = E1.Id
WHERE E1.Name <> E2.Name

INSERT INTO EMPLOYEE2(Id, Name)
SELECT Id, Name FROM EMPLOYEE1 
WHERE Id NOT IN (SELECT Id FROM EMPLOYEE2)

DELETE EMPLOYEE2 WHERE Id NOT IN
(SELECT Id FROM EMPLOYEE1)

ISNULL in sql server

In sql server ISNULL is used to replace one value with some other value. while executing some queries having aggregate functions it may not return the expected result set if table contains NULL values.
For Suppose EMPLOYEE table have the data:

SELECT COUNT(Name) FROM EMPLOYEE
SELECT SUM(Salary) FROM EMPLOYEE

SELECT COUNT(ISNULL(Name,1)) FROM EMPLOYEE
SELECT SUM(ISNULL(Salary,10000)) FROM EMPLOYEE

Above 4 queries will return 3,60000,4,70000 respectively.

Passing database name as parameter in sql server

There is a way to pass database name as NVARCHAR variable to the sql server stored procedures. Only the possible way to utilize this database name is through dynamic sql. There is no use to pass database name as parameter to function because there is no way to write dynamic sql inside function. sample code looks like:

 
CREATE PROCEDURE SPROC_SAMPLE(@DBName NVARCHAR(100))
AS
BEGIN
DECLARE @SCRIPT NVARCHAR(500)
SET @SCRIPT = 'SELECT * FROM [DbName]..TABLENAME'

SET @SCRIPT = REPLACE(@SCRIPT,'[DbName]',@DBName)
EXEC  SP_EXECUTESQL @SCRIPT  
END

EXEC SPROC_SAMPLE 'DatabaseName'

In the above scenario, while executing the procedure whatever database name given, procedure will execute on that database.

Join two tables without any condition only on row by row basis

For suppose EmployeeFirstName and EmployeeLastName tables are like below:

 
SELECT * FROM EMPLOYEEFIRSTNAME
SELECT * FROM EMPLOYEELASTNAME

There is no direct way to join these two tables because there is no common column to establish the relationship. Then, create two temporary tables with identity rownumber column, based on this write the inner join query as follows.

 
CREATE TABLE #FNAME(FName NVARCHAR(100),ROWNUMBER INT NOT NULL IDENTITY (1, 1))
CREATE TABLE #LNAME(LName NVARCHAR(100),ROWNUMBER INT NOT NULL IDENTITY (1, 1))
INSERT INTO #FNAME(FName)
SELECT FName FROM EMPLOYEEFIRSTNAME
INSERT INTO #LNAME(LName)
SELECT LName FROM EMPLOYEELASTNAME
SELECT FN.FName+' '+LN.LName AS FullName FROM #FNAME FN 
INNER JOIN #LNAME LN ON FN.ROWNUMBER = LN.ROWNUMBER

How to change CodeBehind attribute to CodeFile attribute.

Either in user contol or in .aspx page codeBehind attribute appears like this.

<%@Control Language="C#" AutoEventWireup="true" CodeBehind="SampleForm.ascx.cs" Inherits="SampleForm" %>

It inherits from "SampleForm". It is the respective class name in code behind.

public partial class SampleForm {}

Change the CodeBehind attribute to CodeFile as below:

  
<%@Control Language="C#" AutoEventWireup="true" CodeFile="SampleForm.ascx.cs" Inherits="SampleFormChanged" %>

Change respective class name for inherits attribute. Then SampleForm.aspx.cs file class name should be

 
public partial class SampleFormChanged{}

Here there is no need to change any file names. Changing CodeBehind to CodeFile attribute is useful during partial deployment. There is no need to build anything.

Validation for a particular button click event in javascript

In asp.net form assume we have validations for some of the controls, For suppose if we navigate/move from one control to other these validations will fire most of the times. These validations fire for OnChange, OnBlur and other events also. we dont require these validations always. when we submit the form then only we need validations or when we click 'ok/submit' button these should get fire.
Here is the solution for it.
asp.net

  

OnChange="javascript:DropDownList1_OnChange()">



list item1

list item2



ControlToValidate="DropDownList1"

SetFocusOnError="true" 

ErrorMessage="Select a value for 'DropDownList1'"

Display="None" />

javascript

when you navigate to other controls/select items in the drop down list validator fires always.
if you want to stop this behaviour.

  

function pageLoad() {

        DisableAllValidations(false);

}

function DisableAllValidations(enabled1) {

        for (k = 0; k < Page_Validators.length; k++) {

            Page_Validators[i].enabled = enabled1;

            Page_Validators[i].isvalid = enabled1;

        }

    }

it stops all the validations in the pageLoad() itself.
But when you want to enable validations for a particular button click, write below code.

  

    $("input[value='ok']").click(function () {

         DisableAllValidations(true);

         Page_ClientValidate();

         if (!Page_IsValid) {

           DisableValidators(false);

        }

        return Page_IsValid;

      }

Access server contol ID's in javascript without using <% %>

we offen seen this error
"The Controls collection cannot be modified because the control contains code blocks (i.e. <% … %>)."
Here is the fix for it.
For suppose TextBox server contol is like below:

In .cs file write the below code in Page_Load event.

  

protected void Page_Load(object sender, EventArgs e)

{

var javascript = new StringBuilder("var JSClientId = '" + TextBox1.ClientID + "';");

     this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ControlsClientIds", javascript.ToString(), true);

} 

In javascript we can access the TextBox1 server control id as follows