I read this question differently to the posters above me, so my solution is different.
From what I read, you want to copy the entire contents of table1 into table2, then go back to table1 and get rid of the duplicate data in there.
--First of all, we build our test environment
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[table1]')
AND TYPE IN ( N'U' ))
DROP TABLE [dbo].[table1]
CREATE TABLE [table1]
(
places VARCHAR(30)
)
INSERT INTO [table1]
SELECT 'tokyo'
UNION ALL SELECT 'manchester'
UNION ALL SELECT 'new york'
UNION ALL SELECT 'bangkok'
UNION ALL SELECT 'tokyo'
UNION ALL SELECT 'tokyo'
UNION ALL SELECT 'tokyo'
UNION ALL SELECT 'sydney'
UNION ALL SELECT 'sydney'
UNION ALL SELECT 'paris'
UNION ALL SELECT 'paris'
UNION ALL SELECT 'paris'
UNION ALL SELECT 'paris'
UNION ALL SELECT 'manchester'
UNION ALL SELECT 'bangkok'
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[table2]')
AND TYPE IN ( N'U' ))
DROP TABLE [dbo].[table2]
CREATE TABLE [table2]
(
places VARCHAR(30)
)
INSERT INTO [table2]
SELECT 'manchester'
UNION ALL SELECT 'new york'
UNION ALL SELECT 'bangkok'
UNION ALL SELECT 'tokyo'
UNION ALL SELECT 'sydney'
UNION ALL SELECT 'paris'
--Now we can look at your query
INSERT INTO [table2]
SELECT places
FROM [table1];
WITH cte (places, duplicatecount)
AS (SELECT places,
Row_number() OVER(PARTITION BY places ORDER BY places) AS
duplicatecount
FROM [table1])
DELETE FROM cte
WHERE duplicatecount > 1
SELECT *
FROM [table1]
SELECT *
FROM [table2]
DROP TABLE [table1]
DROP TABLE [table2]
/*OUTPUT
table 1
------------------------------
places
------------------------------
new york
bangkok
tokyo
sydney
paris
manchester
table2
------------------------------
places
------------------------------
manchester
new york
bangkok
tokyo
sydney
paris
tokyo
manchester
new york
bangkok
tokyo
tokyo
tokyo
sydney
sydney
paris
paris
paris
paris
manchester
bangkok
*/