SQL Server: Insert a new row for every unique value in column 1

Question

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.

Answer 1

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
);

Answer 2

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.

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.

Answer 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

Answer 4

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:

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