Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert distinct values from one table into another table

Tags:

sql

sql-server

So for each distinct value in a column of one table I want to insert that unique value into a row of another table.

list = select distinct(id) from table0

for distinct_id in list
 insert into table1 (id) values (distinct_id)
end

Any ideas as to how to go about this?

like image 619
Drew Avatar asked Sep 03 '13 18:09

Drew


People also ask

Can you use SELECT distinct and GROUP BY?

Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.

Is distinct better or GROUP BY?

DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY.

Can you use distinct and ORDER BY together?

Without a transformation, a statement that contains both DISTINCT and ORDER BY would require two separate sorting steps-one to satisfy DISTINCT and one to satisfy ORDER BY. (Currently, Derby uses sorting to evaluate DISTINCT.


2 Answers

Whenever you think about doing something in a loop, step back, and think again. SQL is optimized to work with sets. You can do this using a set-based query without the need to loop:

INSERT dbo.table1(id) SELECT DISTINCT id FROM dbo.table0;

There are some edge cases where looping can make more sense, but as SQL Server matures and more functionality is added, those edge cases get narrower and narrower...

like image 116
Aaron Bertrand Avatar answered Oct 03 '22 11:10

Aaron Bertrand


insert into table1 (id)
select distinct id from table0
like image 31
Roman Pekar Avatar answered Oct 03 '22 11:10

Roman Pekar