Update One table, in one D/B, with data from another D/B 1

[Blorf]

Hi. Using Sql Server 2003. I have a two databases running on one server, named Hal2.

First one is called TimeClockPlus.

I want to routinely scan TimeClockPlus, and see one field from one table, and use that info to update a table in a second Database. Second Database is called ToolRoom.

I am somewhat familar with SQL syntax, and creation of Jobs within Sql Server Agent, but I don't know how to reference two d/b's doing this.

Any advice would be helpful.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[SQLDenis]

update t1 set t1.field = t2.field
from TimeClockPlus.dbo.table t1 join
ToolRoom.dbo.table on t1.id =t2.id
where t1.TimeField < t2.TimeField

you qualify with DBName.Owner.Table name if they are on the same server, if they are not then use Servername.DBName.Owner.Table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Blorf]

Thank you Very much Denis, both for the info and for the quick response.

I will see about putting the info to good use.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[CasperTFG]

Hey Blorf why are you doing this through a Job... Do you want the data in DB 1 to be reflected directly into DB 2, if so you could create a trigger on the table.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Blorf]

Hi Denis. Got one new problem that I had not considered.

TimeClock Plus contains an Int type field, for employee ID. ToolRoom contains the same data, but as text. So, I created a View in TimeClock, created an Alias Field, with Str(EmployeeID) but it pads with spaces, so the join will likely fail. I figured that Trim would solve my problem, but the view fails to accept Trim as a valid function.

Any advice?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[SQLDenis]

join on id = convert(int,id)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Sadly, SQL Server does not have a Trim function. It does have LTrim and RTrim. Used together, you get the functional equivalent of Trim.

Select LTrim(RTrim(' This is data. '))


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Blorf]

Not familiar with a trigger.

I chose this method, because in TimeClock plus, there is a punch table. All punches, from the dawn of time are there.

I want only to update the employee table in ToolRoom with the CostCode from TimeClock punch table, but only for the record that has a punch in, and no punch out, ie, current cost code.

So, I built the View, finding those records with

SELECT EmployeeId, TimeOut, CostCode, STR(EmployeeId) AS Joiner
FROM dbo.EmployeeHours
WHERE (TimeOut IS NULL)

Thinkig I could use the recordset as a source for my update.

Hope this explains my goal enough to allow you to help.

Again, thanks.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Blorf]

join on id = convert(int,id)? Sounds too simple, but cool.

L and R trim would work also, I suppose, but the join is one less step.

Thanks Guys!

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Blorf]

I just thought of something.

OK, with what I have so far, the View in TimeClock, why not just have my vb app peek into that view to get the job, instead of updating the table in toolroom. This way, I get real time, instead of once every couple of minutes of fresh data.

Again, thanks!

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[gmmastros]

You should use denis's suggestion regarding the joins (using the Convert(int, EmployeeId) method. I mentioned LTrim and RTrim for information purposes only.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Blorf]

By creating in the TimeClock table the View that only hold the null out punches, I got what I need, and real time (I think), so in my application, when a guy walks up to the tool room, and asks for a tool, bar codes his badge, the tool room app peeks into the view in timeclock to see what job he is clocked into, so all's right with the world.

I have to ask, being new, so don't laugh.

When I peek into the view, assuming that I reinvoke the view each time I need it, the view is returning current data, yes?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[SQLDenis]

The view will always return current data

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Blorf]

Thank you SQLDenis, I appreciate all the help,

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[SQLDenis]

No problem

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/