Help on an Update Job - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help on an Update Job

Author

Topic

Jbalbo
Starting Member

10 Posts

Posted - 2013-10-18 : 16:03:14

Hi

Im looking to create a job that reads one table then takes a value from that table and updates values based on the first table

Example
I have a Provider table has the field OID
I want all OIDS to have at least a given 3 OID_LINKS on the
PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION

I currently get all the values(OIDS) in the PROVIDER table
and manipulate thru excel, then run
INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK) VALUES('0B8B6BDAB32A4124A0F7425D599E829E','CB30DF919B0F468AB203FFD848D11463');

where oid is the PROVIDER.OID and Oid_link is a set value

SO I figure in the end the statement would look like...

select provider.....

INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK) VALUES(PROVIDER.OID,'CB30DF919B0F468AB203FFD848D11463');

Thanks in Advance
Joe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-18 : 17:14:02

INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK)
SELECT OID,'CB30DF919B0F468AB203FFD848D11463'
FROM PROVIDER
WHERE ...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Jbalbo
Starting Member

10 Posts

Posted - 2013-10-21 : 10:03:39

Hi Tara,

Thanks for the info. It is much more straight forward than I was going to do...lol

When I try to test, I get.. Cannot insert duplicate key in object

Because some records already exist on PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION

Can I look at that table before I insert?

Thanks Again..

Joe

Jbalbo
Starting Member

10 Posts

Posted - 2013-10-21 : 10:25:45

So I'm thinking something like...??

INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK)
SELECT dbo.Provider.OID,'CB30DF919B0F468AB203FFD848D11463'
From (
SELECT dbo.Provider.OID
FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION INNER JOIN
dbo.Provider ON dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION.OID = dbo.Provider.OID
) As PI
Where Not Exists (
Select .... ??)

quote:
Originally posted by Jbalbo

Hi Tara,

Thanks for the info. It is much more straight forward than I was going to do...lol

When I try to test, I get.. Cannot insert duplicate key in object

Because some records already exist on PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION

Can I look at that table before I insert?

Thanks Again..

Joe

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 11:14:07

Assuming OID is the column that it is complaining about, you can do the following:

INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK) 
SELECT OID,'CB30DF919B0F468AB203FFD848D11463'
FROM PROVIDER p
WHERE NOT EXISTS 
(
	SELECT * FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION c
	WHERE c.OID = p.OID
);

Jbalbo
Starting Member

10 Posts

Posted - 2013-10-21 : 11:28:23

Hi James,

Thank you..

I even understand it!!! :)

If I wanted to incorporate two other OIDS in
SELECT OID,'CB30DF919B0F468AB203FFD848D11463' so it also updates two others.

Should I create a job with three statements?

or is that sloppy?

Thanks Again
Joe

quote:
Originally posted by James K

Assuming OID is the column that it is complaining about, you can do the following:
INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK)
SELECT OID,'CB30DF919B0F468AB203FFD848D11463'
FROM PROVIDER p
WHERE NOT EXISTS
(
SELECT * FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION c
WHERE c.OID = p.OID
);

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 11:31:23

If it is a total of just three OID's that you want to insert, just use the statement 3 times. The KISS principle, you know :) (Keep It Simple and Sweet). If your requirement was to insert a bunch of OID's, then we would have to think of something else.

Jbalbo
Starting Member

10 Posts

Posted - 2013-10-21 : 16:25:32

Thank you both for the help..

This seems to work now just had to match on provider

INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK)
SELECT OID,'91E3AF792608402CA226F68F2EB76415'
-- This is Kevin B
FROM PROVIDER p
WHERE NOT EXISTS
(
SELECT * FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION c
WHERE c.OID_LINK = '91E3AF792608402CA226F68F2EB76415'
-- This is Kevin B
and c.OID = P.OID
-- This is the individual provider

Thanks
Joe

quote:
Originally posted by Jbalbo

Hi James,

Thank you..

I even understand it!!! :)

If I wanted to incorporate two other OIDS in
SELECT OID,'CB30DF919B0F468AB203FFD848D11463' so it also updates two others.

Should I create a job with three statements?

or is that sloppy?

Thanks Again
Joe

quote:
Originally posted by James K

Assuming OID is the column that it is complaining about, you can do the following:
INSERT INTO dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION (OID, OID_LINK)
SELECT OID,'CB30DF919B0F468AB203FFD848D11463'
FROM PROVIDER p
WHERE NOT EXISTS
(
SELECT * FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION c
WHERE c.OID = p.OID
);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources