SQL questions - 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 2012 Forums

Transact-SQL (2012)

SQL questions

Author

Topic

schorn
Starting Member

2 Posts

Posted - 2014-07-07 : 10:31:15

I have little experience with SQL and need some help. I am dealing with the following two tables:

[SFA-DCSVR\LASERFICHE].swmv8.dbo.toc
[SFA-DCSVR].GoldMine.dbo.contact1

I linked swmv8 to goldmine. I would like to update a field tocid in the goldmine.contact1 table with the field tocid value that is in swmv8.toc table.

I would like this update to automatic on a periodic basis.

I have a contact field in goldmine.contact1 which has the client's firstname lastname.

I have name field in the swmv8.toc table that has the client's lastname, firstname.

I also have parentid field in the swmv8.toc table that will need to equal 14991. Here is the sql query have so far.

select tocid, parentid, name from [SFA-DCSVR\LASERFICHE].swmv8.dbo.toc
where name = (select lastname from [SFA-DCSVR].GoldMine.dbo.contact1)+', '+
(SELECT Left(contact, CHARINDEX(' ',contact + ' ' ))
from [SFA-DCSVR].GoldMine.dbo.contact1)
and parentid='14991'

The query editor has errors displayed in it for the above query.

As I see it, I need to get the query correct and add language to store the tocid field from the swmv8.dbo.toc table to the goldmine.dbo.contact1 tocid field.

To complicate it further I would like to have this update for new clients created.

I am happy to hire someone to help with the sql programming.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-07-07 : 10:59:52

check out the below query:

select tocid, parentid, name from [SFA-DCSVR\LASERFICHE].swmv8.dbo.toc a
join [SFA-DCSVR].GoldMine.dbo.contact1 b
on a.name= (select substring(b.contact,(CHARINDEX(' ',b.contact)+1),len(b.contact)-CHARINDEX(' ',b.contact))+','+ (select substring(b.contact,1,CHARINDEX(' ',b.contact))))
where a.parentid=14991

Hema Sunder

schorn
Starting Member

2 Posts

Posted - 2014-07-07 : 19:12:18

Thank you Hema. This works and displays the swmv8.tocid for each name.

How do I update the goldmine.tocid with the swmv8.tocid.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-07-08 : 05:38:30

begin tran
update b
set b.tocid = a.tocid
from [SFA-DCSVR].GoldMine.dbo.contact1 b
join [SFA-DCSVR\LASERFICHE].swmv8.dbo.toc a
on a.name= (select substring(b.contact,(CHARINDEX(' ',b.contact)+1),len(b.contact)-CHARINDEX(' ',b.contact))+','+ (select substring(b.contact,1,CHARINDEX(' ',b.contact))))
where a.parentid=14991

--commit tran

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources