Inserting unique values into a different tables if they don''t exists already.

Hi

I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M

INNER JOIN _MemberProfileLookupValues ML

ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND OptionID <> 6

When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID

I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately

ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.

Any ideas / help will be greatly appreciated. Please help.

Kind Regards

Carel Greaves

The following query insert the new members who option id 6 is not exist in the MemberProfileLookupValues table,

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT

M.MemberID

, '6'

, CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM

Members M

Where

NOT EXISTS

(

Select 1 From _MemberProfileLookupValues ML

Where M.MemberID = ML.MemberID And OptionID = 6

)

And M.Active = 1

|||

Thanks a lot.

That is exactly what i was looking for.

Kind Regards

Carel Greaves

Inserting records that don't already exist

There is a good article at
http://support.microsoft.com/defaul...kb;en-us;315968 that shows ho
w
to use OPENXML to update records that exist and add new records that don't t
o
a table.
The question is how do you modify the SQL code to work with a table with a
three column primary key? The IN statement only works when selecting a
single column.
Any help would be much appreciated.
Thanks,
OldmanHi
You can try this way
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
FROM OPENXML (@.hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
XMLEmployee
INNER JOIN Employee
Where Employee.EmployeeId <> XMLEmployee.EmployeeID AND
Employee.FirstName <> XMLEmployee.FirstName AND
Employee.LastName <> XMLEmployee.LastName
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Oldman" wrote:

> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
You can use NOT EXISTS instead of NOT IN:
WHERE NOT EXISTS
(SELECT *
FROM Employee e
WHERE e.Col1 = XMLEmployee.Col1 AND
e.Col2 = XMLEmployee.Col2 AND
e.Col3 = XMLEmployee.Col3)
Hope this helps.
Dan Guzman
SQL Server MVP
"Oldman" <Oldman@.discussions.microsoft.com> wrote in message
news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
> how
> to use OPENXML to update records that exist and add new records that don't
> to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Let us assume that the pk is (employeeid, firstname, lastname), then you can
do:
insert into employee
select
employeeid,
firstname,
lastname
from
openxml (@.hdoc, '/newdataset/employee',1)
with (employeeid integer, firstname varchar(100), lastname varchar(100))
as x
left join
employee as e
on
e.employeeid = x.employeeid and
e.firstname = x.firstname and
e.lastname = x.lastname
where
e.employeeid is null
and e.firstname is null
and e.lastname is null
go
AMB
"Oldman" wrote:

> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Thanks guys for your answers. Dan that worked!
Now that you told me I'm smacking my head because I knew of the EXISTS
keyword.
Thanks again.
"Dan Guzman" wrote:

> You can use NOT EXISTS instead of NOT IN:
> WHERE NOT EXISTS
> (SELECT *
> FROM Employee e
> WHERE e.Col1 = XMLEmployee.Col1 AND
> e.Col2 = XMLEmployee.Col2 AND
> e.Col3 = XMLEmployee.Col3)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oldman" <Oldman@.discussions.microsoft.com> wrote in message
> news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
>
>

Inserting records back into temp table that don't exist

I have a procedure that I'm trying to produce for a client. They want to see
total order counts each day from the 1st to the end of the month.
The procedure I have now will produce order counts for days that do exist. I
t
is using the order open date to pull orders from the orders table from
between a start and end date.
The client wants to see zero for the days that didn't have any activity.
For example:
Office Day Total
BranchA 1 50
BranchA 2 0
BranchA 3 10
How do I insert a blank record into my temp table that will show 0 for the
days that didn't pull?
Thanks so much in advance.
Message posted via http://www.webservertalk.comA general method is to create a dataset/table/view with all the days you
want to include and use an OUTER JOIN to get the results. If you want
specific query, please read www.aspfaq.com/5006 and post relevant
information to repro your problem.
Anith