selecting & then inserting unique rows

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

General SQL Server Forums

New to SQL Server Programming

selecting & then inserting unique rows

Author

Topic

sherlock_oz
Starting Member

12 Posts

Posted - 2007-09-18 : 02:11:00

As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A

tried a few different ways & just keep getting strange errors that refer to courier font ??

SQL Team Your my Hero !

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-18 : 02:22:01

Here is a query you can use to SELECT the rows in TABLE_B that aren't in TABLE_A


SELECT name  from TABLE_B
WHERE name NOT IN (
  SELECT name 
  FROM TABLE_A )
ORDER BY name DESC

Something like that should work. You can also change this query pretty easily to an INSERT statement once you are certain the results are correct and should be inserted into TABLE_A.

-ec

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 02:23:49

Are you trying to do this?

declare @A table ([name] nvarchar(200))
declare @B table ([name] nvarchar(200))
insert into @A ([name]) select 'jim'
insert into @A ([name]) select 'jon'
insert into @A ([name]) select 'robyn'
insert into @A ([name]) select 'fred'
insert into @B ([name]) select 'jim'
insert into @B ([name]) select 'jon'
insert into @B ([name]) select 'sherlock_oz'

insert into @A (name)
select [name] from @B where [name] not in (select [name] from @A)

select [name] from @A

(sorry eyechart - you just beat me to it)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 02:24:06

insert table2 (col1)
select t1.col1
from table1 as t1
where not exists (select * from table2 as t2 where t2.col1 = t1.col1)

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

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-18 : 02:35:30

quote:
Originally posted by rrb

(sorry eyechart - you just beat me to it)

ftw

-ec

sherlock_oz
Starting Member

12 Posts

Posted - 2007-09-18 : 02:37:18

WOW, BAM KAPOWW

I will let you know how i get on , after my day job !

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 02:42:44

yes - although actually your topic is about inserting unique rows, so maybe you should actually have

insert into @A (name)
select DISTINCT [name] from @B where [name] not in (select [name] from @A)

(sorry eyechart - couldn't resist)

ftw! indeed.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-18 : 02:48:00

quote:
Originally posted by rrb

ftw! indeed.

Ha!

:)

-ec

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 02:56:47

there once was a fellow named james
who liked to win! when he played games
not to be gypped
he posted his script
but forgot to distinguish his names

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-18 : 13:17:38

Here is another way to do it:

INSERT 
	@A 
SELECT
	DISTINCT B.Name
FROM
	@B AS B
LEFT OUTER JOIN
	@A AS A
	ON B.Name = A.Name
WHERE
	A.Name IS NULL

sherlock_oz
Starting Member

12 Posts

Posted - 2007-09-18 : 20:15:56

i have all that sorted now , Thanks so much

But i still dont get the thing about Yaks & Owing lots of money ??

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 20:53:08

Yak - the sqlteam familiar. You'll just have to think about owing me lots of money....

Glad to hear you're all sorted. (AND THAT YOU'RE AUSTRALIAN!)

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources