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?
关于如何解决这个问题的任何想法?
4 个解决方案
#1
21
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:
每当你考虑在循环中做某事时,退后一步,再想一想。 SQL已经过优化,可以使用集合。您可以使用基于集合的查询执行此操作,而无需循环:
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...
有一些边缘情况,循环可以更有意义,但随着SQL Server的成熟和更多功能的添加,这些边缘情况变得越来越窄......
#2
5
insert into table1 (id)
select distinct id from table0
#3
1
The following statement works with me.
以下声明适用于我。
insert into table1(col1, col2) select distinct on (col1) col1 col2 from table0
#4
0
Other Simple way to copy distinct data with multiple columns from one table to other
其他使用多个列从一个表复制到另一个表的不同数据的简单方法
Insert into TBL2
Select * from (Select COL1, ROW_NUMBER() over(PARTITION BY COL1 Order By COL1) AS COL2 From TBL1)T
where T.COL2 = 1
#1
21
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:
每当你考虑在循环中做某事时,退后一步,再想一想。 SQL已经过优化,可以使用集合。您可以使用基于集合的查询执行此操作,而无需循环:
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...
有一些边缘情况,循环可以更有意义,但随着SQL Server的成熟和更多功能的添加,这些边缘情况变得越来越窄......
#2
5
insert into table1 (id)
select distinct id from table0
#3
1
The following statement works with me.
以下声明适用于我。
insert into table1(col1, col2) select distinct on (col1) col1 col2 from table0
#4
0
Other Simple way to copy distinct data with multiple columns from one table to other
其他使用多个列从一个表复制到另一个表的不同数据的简单方法
Insert into TBL2
Select * from (Select COL1, ROW_NUMBER() over(PARTITION BY COL1 Order By COL1) AS COL2 From TBL1)T
where T.COL2 = 1