SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help on an Update Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jbalbo
Starting Member

10 Posts

Posted - 10/18/2013 :  16:03:14  Show Profile  Reply with Quote
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

USA
35940 Posts

Posted - 10/18/2013 :  17:14:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Jbalbo
Starting Member

10 Posts

Posted - 10/21/2013 :  10:03:39  Show Profile  Reply with Quote
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

Go to Top of Page

Jbalbo
Starting Member

10 Posts

Posted - 10/21/2013 :  10:25:45  Show Profile  Reply with Quote
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




Edited by - Jbalbo on 10/21/2013 10:27:59
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/21/2013 :  11:14:07  Show Profile  Reply with Quote
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
);
Go to Top of Page

Jbalbo
Starting Member

10 Posts

Posted - 10/21/2013 :  11:28:23  Show Profile  Reply with Quote
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
);


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/21/2013 :  11:31:23  Show Profile  Reply with Quote
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.
Go to Top of Page

Jbalbo
Starting Member

10 Posts

Posted - 10/21/2013 :  16:25:32  Show Profile  Reply with Quote

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
);




Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000