Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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

3873 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

3873 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  
 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.16 seconds. Powered By: Snitz Forums 2000