June 24, 2010 at 2:59 am
hi guys,
can you give me some ideas how to do this? i have table1 and table2. i want to copy the contents of table1 to table2 and "prune" table1 with only 1 occurence of each place.
table1:
places
-----------
tokyo
manchester
new york
bangkok
tokyo
tokyo
tokyo
sydney
sydney
paris
paris
paris
paris
manchester
bangkok
table2:
places
--------
manchester
new york
bangkok
tokyo
sydney
paris
copy table1 to 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
and "prune" table1 with only 1 occurance of each place:
table1:
places
-----------
tokyo
manchester
new york
bangkok
sydney
paris
thanks for any advice and suggestions.
June 24, 2010 at 3:01 am
This should do the trick:
INSERT INTO Table2
SELECT DISTINCT Places
FROM Table1 AS A
WHERE NOT EXISTS (
SELECT 1
FROM Table2 AS B
WHERE A.Places = B.Places
)
-- Gianluca Sartori
June 24, 2010 at 3:18 am
monzulu (6/24/2010)
i want to copy the contents of table1 to table2 and "prune" table1 with only 1 occurence of each place......
thanks for any advice and suggestions.
SELECT ...UNION... also can be used to achieve this.
June 24, 2010 at 3:42 am
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
*/
June 24, 2010 at 8:35 am
thanks guys for you inputs.
skcadavre thanks very much also. i'll give it a try. that looks like it's what i need.
have a nice day!
June 24, 2010 at 8:50 am
Here is minimalist approach (use setup provided by skcadavre):
DELETE dbo.table1 OUTPUT deleted.places INTO dbo.table2 (places)
INSERT INTO dbo.table1 (places)
SELECT DISTINCT places FROM dbo.table2
There is a setback here: if, at the start, you would have some place in Table2 which is not in Table1, you will see it in Table1 as well after execution of this query...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply