joins - SQL Server Forums

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

joins

Author

Topic

anjali66
Starting Member

23 Posts

Posted - 2011-01-11 : 17:36:48

hI,

I have two tables one is lcm and another one is lcmmaster. I need to insert all the records from lcm to lcm_master based on the condition that when Id of lcm does not match the id of lcm_master.

so i wrote this query

SELECT lcm
FROM lcm B
where ID NOT IN ( SELECT ID FROM lcmMaster)

I don't get any rows from the above query, but when I do

select * from dbo.lcm where Id = '2022' -- i get 1 row back

select * from dbo.lcmMaster where Id ='2022' -- i don't get anything back

I am not sure what am I don't wrong.

All I wanted to do is insert all the records from lcm table to lcmMaster table whose ID does not match.

I tried to write this query for that
Insert into MCL_Master_DL_B
(
col1,
ID)

SELECT
B.col1,
ID
FROM lcm B , lcmMaster
where lcmMaster.id <> B.id (This <> is producing miliions of rows and I don't have that many rows in either of my table)

Please let me know what am I doing wrong or if there is any better way to do this.

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-11 : 17:52:08

--INSERT INTO lcm_master (...)
SELECT ...
FROM lcm l
WHERE NOT EXISTS (SELECT * FROM lcm_master m WHERE l.Id = m.Id)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

anjali66
Starting Member

23 Posts

Posted - 2011-01-11 : 18:29:51

Thanks Tara.

I appreciate all your help!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-11 : 18:46:33

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 10:45:44

Hi,

I tried this statement
--INSERT INTO lcm_master (col1, col2, col3)
SELECT ID
FROM lcm l
WHERE NOT EXISTS (SELECT * FROM lcm_master m WHERE l.Id = m.Id and l.col1=m.col1 and l.col2=m.col2 and l.col3=m.col3)

and the select query is returning all the Id's that exists in lcm_master. To test it whether the above query is returning all the rows that exists in lcm_master, I did this

select * from lcm_master where lid in (
'405',
'208',
'145',
'858',
'594',
'981'

)

I am not sure what I need to do to insert all the records that exists in lcm and not in lcm_master.

Please let me know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 10:50:36

it might be that ids exist in lcm_master but did you check values of other fields too (col1 ,col2,col3).
please keep in mind that NOT EXISTS condition checks for combination of id,col1,col2,col3 rather than id alone so even if id exists in lcm_master if it doesnt have same value for col1,col2,col3 in lcm_master it will still be returned from lcm

if you specifically want to look for id alone what you need is:-


SELECT ID
FROM lcm l
WHERE NOT EXISTS (SELECT * FROM lcm_master m WHERE l.Id = m.Id)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 10:55:05

Hi Visakh,

You are right. Is their any way, I can write this query that inserts the records that exists in lcm and do not exists in lcm_master and Update the records in lcm_master that exists both in lcm_master and lcm.
I have around 4876 records in lcm.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:03:21

so you want to check existence only based on id alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 11:17:21

No,I have 6 columns. i want to che check based on all these 6 columns.
is it possible to do that. I don't want to end uo with dup. in my resulting table, but I guess there will be some duplicates when only 4 columns are matching and rst of the columns are not matching then I need to insert that record instead of updating it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:31:32

what? do you mean all 6 columns make up your unique value combination?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 13:04:51

I need to talk to my boss about this table. I am not sure about the unique key here

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:09:49

ok. then first post it and we will help you further

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources