Lets say we have a table below

TABLE1

ID         Roll        Name         Amount     . . . . . . so on
---------------------------------------
1            2           Alex            500
2            5           Jones          600
3            2           Ales            400

and we have TABLE 2

ID         Roll        Name         Amount   . . . . . . . so on
---------------------------------------

In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.

I want to perform a query in such a way that SQL Should,

1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2

So we should get the result similar to below

ID         Roll        Name         Amount   . . . . . . . so on
---------------------------------------
1            2           Alex            500
3            2           Ales            400

Can somebody help me up of how can I do this in a same sql statement, Thanks.

There are two ways to delete all rows in a table.  The first requires more permission but is faster:

truncate table Table2;

delete from Table2;

To insert you need to use the set identity insert on for table2 https://msdn.microsoft.com/en-us/library/ms188059.aspx?f=255&MSPPError=-2147217396.  This allows you to insert a value into ID.  To use this you need to list every column that you are inserting to.

set identity_insert TABLE2 ON;

insert TABLE2 (ID, Roll, Name, Amount, ...)
select ID, Roll, Name, Amount, ...
from TABLE1
where Roll = 2;

set identity_insert TABLE2 OFF;

Actually ,See below how can you insert into a table having identity column.

-- Create tool table.
CREATE TABLE dbo.Tool(
   ID INT IDENTITY NOT NULL PRIMARY KEY, 
   Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO

-- Create a gap in the identity values.
DELETE dbo.Tool 
WHERE Name = 'Saw'
GO

SELECT * 
FROM dbo.Tool
GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO

SELECT * 
FROM dbo.Tool
GO
-- Drop products table.
DROP TABLE dbo.Tool
GO