SQL Server:为第1列中的每个惟一值插入一个新行。

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.

使用SQL Server，我尝试查找表的第1列中的每个惟一值，然后使用惟一的第1列值插入新的行，并添加第2列值。第2列的值每次都是相同的。

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.

注意:我可以从第1列的数据库中取出惟一的值，并为每个值添加一个插入，但是在第1列中我有160万个惟一的值，所以这样写很烦人。

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

以列1中的2个唯一值为例:

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.

现在，我想为每个MacAddr值添加PackageId = 37，但是到目前为止，写一些东西来查找并添加唯一的值是不可能的。如前所述，我可以通过为每个MacAddr编写一个插入到脚本中来轻松实现这一点，但这需要花费160万MacAddr值。

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.

谢谢你的帮助。

4 个解决方案

#1

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

这将插入一个记录，记录每个不同的MacAddr，其中PackageId值为37，而PackageId值为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
);

#2

To INSERT a new record into the SettopSubscription table for each unique value of MacAddr, with a PackageId of 37 (not inserting if there is already a record in the table for that combination of MacAddr and PackageId:

将一个新记录插入到SettopSubscription表中，为MacAddr的每一个唯一值，使用PackageId 37(如果在表中已经有了MacAddr和PackageId的组合的记录，则不插入):

INSERT INTO SettopSubscription (MacAddr, PackageId)
SELECT DISTINCT s1.MacAddr, 37 
FROM dbo.SettopSubscription s1
LEFT JOIN dbo.SettopSubscription s2 ON s1.MacAddr = s2.MacAddr 
                                   AND s2.PackageId = 37
WHERE s2.MacAddr IS NULL

#3

try this:

试试这个:

insert into dbo.SettopSubscription (MacAddr,PackageId)   
select MacAddr, 'your_PackageId_Value'  from 
(select count(dbo.SettopSubscription.MacAddr ) nbr, MacAddr  from
dbo.SettopSubscription group by MacAddr ) tmp where tmp.nbr=1

#4

It sounds like you want this:

听起来你想要的是:

declare @pid int
set @pid=37
insert into dbo.SettopSubscription (macaddr, packageid)
select distinct ss.macaddr, @pid
from dbo.SettopSubscription ss
where not exists (
    select * from dbo.SettopSubscription ss2 
    where ss2.macaddr=ss.macaddr and ss2.packageid=@pid)

For every unique macaddr in column 1, this is just inserting a record with the packageid you indicated and making sure the combination does not yet exist.

对于第1列中每一个独特的macaddr，这只是插入一个记录与packageid您指出并确保组合还不存在。

I added the variable under the assumption you might reuse the query. Putting a literal 37 in multiple locations can easily lead to data issues if on a future run, you only update the first use of the literal.

我在假设中添加了变量，您可以重用该查询。如果在以后的运行中，只更新第一次使用的文字，那么将文字37放在多个位置很容易导致数据问题。

#1