Insert unique rows into existing table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert unique rows into existing table?

Author

Topic

Amberite
Starting Member

11 Posts

Posted - 2007-09-05 : 16:04:21

Hello,

I have a table, @table1, which has several rows with two columns: id, count. I need to do another SELECT statement, and insert its results into @table1, but ONLY on rows that dont already exist in @table1. I think this is pretty simple, but I'm not too familiar with how to do this. Here is a quick example:

@table1:              SELECT results
id    count           id
-----------------     -----------------
1     39              1
2     13              2
3     19              3
                      4
                      5

Will become:

@table1:
id    count
-----------------
1     39
2     13
3     19
4     0
5     0

Any ideas? Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 16:11:57

select r.id, 0 as count
from results as r
left join @table1 as t on t.id = r.id
where t.id is null

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-09-05 : 16:16:40

Hi Peso,

Thanks for your reply. The thing is I want to keep the original @table1, and just insert the rows into THAT table. Is that possible? Or is it just better if I use your method and work around it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 16:46:33

What is there to work around?

insert @table1
select r.id, 0 as count
from results as r
left join @table1 as t on t.id = r.id
where t.id is null

E 12°55'05.25"
N 56°04'39.16"

Amberite
Starting Member

11 Posts

Posted - 2007-09-05 : 16:48:42

Never mind.. I'm a little stupid. Thanks for your help peso! :)

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 04:06:22

Alternatively use the NOT EXISTS approach


insert @table1
select r.id, 0 as count
from results as r
where NOT EXISTS
(
    SELECT *
    FROM @table1 as t
    WHERE t.id = r.id
)

which is sometimes easier for newbies to understand (although I prefer the LEFT JOIN approach myself)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources