— Copy all the values from one column to another column in table ( PAWAN)

–Create a table
CREATE TABLE CopyColumns
(

    Name varchar(50) NOT NULL,
    Address varchar(70) NOT NULL,
    CopyAddress varchar(70) NULL

)

–Insert the values
INSERT INTO CopyColumns ( Name , Address )
VALUES ( ‘Pawan’  ,  ‘Pune ‘)

INSERT INTO CopyColumns ( Name , Address )
VALUES ( ‘Gauri’  ,  ‘A  Nagar ‘)

INSERT INTO CopyColumns ( Name , Address )
VALUES ( ‘Saurabh’  ,  ‘A Nagar ‘)

INSERT INTO CopyColumns ( Name , Address )
VALUES ( ‘Himanshu’  ,  ‘Padmavati ‘)

INSERT INTO CopyColumns ( Name , Address )
VALUES ( ‘Rahul W’  ,  ‘ Jordan’)


— Use the below query to copy all the data from Address to CopyAddress column
UPDATE CopyColumns SET CopyAddress = ( SELECT Address FROM CopyColumns C1 WHERE C1.Name = CopyColumns.Name )

–Check
SELECT Name , Address , CopyAddress from CopyColumns___________
Pawan Kumar
pawankkmr@hotmail.com

———-USE OF Common Type Expressions ( CTE )

— FIND OUT 2nd LAST INSERTED ROW WITHOUT USING INDEX / IDENTITY COLUMN / TIME STAMP COLUMN ( PAWAN )

--Create a table
CREATE TABLE TestLastQuery
(

Name varchar(50),
Address varchar(70)

)

--Insert the values
INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Pawan'  ,  'Pune ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Gauri'  ,  'A  Nagar ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Saurabh'  ,  'A Nagar ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Himanshu'  ,  'Padmavati ')

INSERT INTO TestLastQuery ( Name , Address )
VALUES ( 'Rahul W'  ,  ' Jordan')

-- Use the below query to find out the last inserted row
WITH CTE
AS
(
SELECT TOP 2 Name , Address , row_number() over ( order by ( SELECT 2 ) )
AS ROWNUMBER FROM TestLastQuery ORDER BY  ROWNUMBER DESC
)
SELECT TOP 1 Name , Address from CTE ORDER BY ROWNUMBER
GO

____

Pawan Kumar

pawankkmr@hotmail.com