SQL Server:为列1中的每个唯一值插入新行 [英] SQL Server: Insert a new row for every unique value in column 1

问题描述

使用SQL Server，我试图在表的第1列中查找每个唯一值，然后使用该唯一列1值并添加第2列值来插入新行.列2的值每次都相同.

要注意:我可以通过从第1列的数据库中提取唯一值并为每个值添加一个插入来实现，但是我在第1列中有160万个唯一值，因此以这种方式编写会很烦人./p>

以第1列中的2个唯一值为例进行显示:

 选择*来自dbo.SettopSubscription其中MacAddr ='0000000ee4b5'或MacAddr ='0000003a9667' 

结果:

  MacAddr PackageId------------ -----------0000000ee4b5 110000000ee4b5 30000003a9667 2410000003a9667 2410000003a9667 110000003a9667 2110000003a9667 80000003a9667 44110000003a9667 44120000003a9667 4479 

现在，我想将PackageId = 37添加到每个唯一的MacAddr值，但是到目前为止，写一些东西来查找并仅添加唯一值并没有运气.如前所述，我可以通过为每个MacAddr的脚本中插入一个插入内容来轻松做到这一点，但这要花掉160万个MacAddr值.

开始视图，与上面相同:

  MacAddr PackageId------------ -----------0000000ee4b5 110000000ee4b5 30000003a9667 2410000003a9667 2410000003a9667 110000003a9667 2110000003a9667 80000003a9667 44110000003a9667 44120000003a9667 4479 

最终结果:

  MacAddr PackageId------------ -----------0000000ee4b5 110000000ee4b5 3*** 0000000ee4b5 37 ***0000003a9667 2410000003a9667 2410000003a9667 110000003a9667 2110000003a9667 80000003a9667 44110000003a9667 44120000003a9667 4479*** 0000003a9667 37 *** 

感谢您的帮助.

解决方案

这将为每个不同的 MacAddr 插入一条记录，其 PackageId 为 37 37 的 PackageId 的code>:

 插入SettopSubscription(MacAddr，PackageId)选择不同的s1.MacAddr，37来自SettopSubscription s1不存在的地方(选择s2.PackageId来自SettopSubscription s2其中s2.MacAddr = s1.MacAddr和s2.PackageId = 37); 

Using SQL Server I am trying to find every unique value in column 1 of a table, and then insert a new row using that unique column 1 value and adding a column 2 value. Column 2 value will be the same every time.

To note: I could do this by pulling out the unique values from the database on column 1 and adding an insert for each but I have 1.6 million unique values in column 1 so it would be tiresome to write it that way.

Taking 2 unique value from column 1 to show this as an example:

select *
from dbo.SettopSubscription
where MacAddr = '0000000ee4b5'
   or MacAddr = '0000003a9667'

Results:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479

Now I want to add PackageId = 37 to every unique MacAddr value but so far no luck in writing something to find and add on only the unique values. As stated before I can do this easily by writing an insert into script for each MacAddr but that was take forever on 1.6 million MacAddr values.

Beginning view, same as above:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479

End result:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
***0000000ee4b5    37***
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479
***0000003a9667    37***

Thanks for the help ahead of time.

解决方案

This will insert a record for each distinct MacAddr with a PackageId of 37 that does not already have a PackageId of 37:

insert into SettopSubscription (MacAddr, PackageId)
select distinct s1.MacAddr, 37 
from SettopSubscription s1
where not exists
(
    select s2.PackageId
    from SettopSubscription s2
    where s2.MacAddr = s1.MacAddr
    and s2.PackageId = 37
);

这篇关于SQL Server:为列1中的每个唯一值插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！