Finding duplicate values in a SQL table

SELECT NAME, COUNT (NAME)'COUNT' 

FROM TEST 

GROUP BY NAME 

HAVING COUNT (NAME) > 1

UPDATE and REPLACE part of a string in sql server

COLUMN_NAME = 'RAM'

UPDATE TABLE_NAME SET COLUMN_NAME = REPLACE (COLUMN_NAME,'M','MA')

Copy one table to another table

SQL

SELECT * INTO NEW_TABLE_NAME FROM OLD_TABLE_NAME

PL SQL

CREATE TABLE NEW_TABLE_NAME AS SELECT * FROM OLD_TABLE_NAME

Selecting data from different servers in SQL Server

SYNTAX:

SELECT * FROM [SERVER NAME/IP].[DATABASE NAME].[OWNER].[TABLE NAME]

NOTE:-

Don’t forgot to create the Linked Servers

Reset identity column in sql server

SYNTAX:

DBCC CHECKIDENT ('TEST', RESEED, 0)

Lets Explorer It…….

STEP 1:- CREATE TABLE

CREATE TABLE TEST

(

       SRNO INT IDENTITY (1, 1),

       NAME VARCHAR (100)

)

STEP 2:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 3:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

(2 ROW(S) AFFECTED)

STEP 4:- RESET IDENTITY

DBCC CHECKIDENT ('TEST', RESEED, 0)

MESSAGES

CHECKING IDENTITY INFORMATION: CURRENT IDENTITY VALUE '2'.

DBCC EXECUTION COMPLETED. IF DBCC PRINTED ERROR MESSAGES, CONTACT YOUR SYSTEM ADMINISTRATOR.

STEP 5:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 6:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

1           RAM

2           RAM

change input to upper case

Using Javascript

<asp:TextBox onkeypress="javascript:this.value=this.value.toUpperCase();"  onkeyup="javascript:this.value=this.value.toUpperCase();" ID="TextBox1" runat="server"></asp:TextBox>

Using CSS

<style>

    .uppercase {

    text-transform: uppercase;

}

</style>

<asp:TextBox CssClass="uppercase" ID="TextBox1" runat="server"></asp:TextBox>

How to set identity column off and on in sql server

SYNTAX:

OFF IDENTITY

SET IDENTITY_INSERT TEST ON

ON IDENTITY

SET IDENTITY_INSERT TEST OFF

Lets Explorer It…….

STEP 1:- CREATE TABLE

CREATE TABLE TEST

(

       SRNO INT IDENTITY (1, 1),

       NAME VARCHAR (100)

)

STEP 2:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 3:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

(2 ROW(S) AFFECTED)

STEP 4:- SET IDENTITY ON (MEANS DISABLE THE AUTO IDENTITY NUMBER GENERATION)

SET IDENTITY_INSERT TEST ON

STEP 5:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

RESULTS

MSG 545, LEVEL 16, STATE 1, LINE 26

EXPLICIT VALUE MUST BE SPECIFIED FOR IDENTITY COLUMN IN TABLE 'TEST' EITHER WHEN IDENTITY_INSERT IS SET TO ON OR WHEN A REPLICATION USER IS INSERTING INTO A NOT FOR REPLICATION IDENTITY COLUMN.

STEP 6:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (SRNO, NAME) VALUES (10, 'RAM')

STEP 7:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

10          RAM

(3 ROW(S) AFFECTED)

STEP 8:- SET IDENTITY OFF (MEANS ENABLE THE AUTO IDENTITY NUMBER GENERATION)

SET IDENTITY_INSERT TEST OFF

STEP 9:- INSERT THE VALUES IN THE TABLE

INSERT INTO TEST (NAME) VALUES ('RAM')

STEP 10:- SELECT THE RECORD FROM TABLE

SELECT * FROM TEST

RESULTS

SRNO        NAME

----------- -------

1           RAM

2           RAM

10          RAM

11          RAM

(4 ROW(S) AFFECTED)