sql server - automated task

[Mattastic]

Hi,

Is there anyway I can tell SQL server, to update a table in one db, with info
from a table in a diffferent db, each morning at a set time?

Currently I use scheduled tasks in CF, I would like to do all this in SQL
server though if possible.

Thankyou

[PaulH *ACE*]

Mattastic wrote:

> Hi,
>
> Is there anyway I can tell SQL server, to update a table in one db, with info
> from a table in a diffferent db, each morning at a set time?

schedule a job via enterprise manager. under management, sql server agent (which
needs to be running to use the scheduler). it's pretty straight forward.

[Mattastic]

Thanks for your reply.

However, when I start a new job, I can only select tables from my current db, do you know how i can select a table from a different db?

Thankyou

[PaulH *ACE*]

Mattastic wrote:

> However, when I start a new job, I can only select tables from my current db, do you know how i can select a table from a different db?

how are you doing it now? just stuff your sql into each step you need. if you're
doing it via cf (ie using cfoutput over cfquery) post your code.

[Mattastic]

I can figure out how to do it within a single db, table to table.

But, i have db on a different server, Im not sure how i can get data from that.

Thanks again

[PaulH *ACE*]

Mattastic wrote:

> I can figure out how to do it within a single db, table to table.
>
> But, i have db on a different server, Im not sure how i can get data from that.

as long as you have a user w/permissions & this is on the same sql server, the
syntax is always the same:

dbName.dbOwner.tableName

SELECT name,accountBalance
FROM customer
INNER JOIN otherDB.dbo.accounting
ON customer.customerID = otherDB.dbo.accounting.customerID

[PaulH *ACE*]

Mattastic wrote:

> But, i have db on a different server, Im not sure how i can get data from that.

messages seem to be doubling up....oh well, trying again.

as long as you have a user w/permissions & this is on the same sql server, the
syntax is always the same:

dbName.dbOwner.tableName

SELECT name,accountBalance
FROM customer
INNER JOIN otherDB.dbo.accounting
ON customer.customerID = otherDB.dbo.accounting.customerID

[philh]

If the tables are on separate instances or separate physical servers, the only
way to do this is to create a link server entry, which you can refer to in your
SQL statements through four-part naming.

So, if I have two servers, SQL1 and SQL2, and I want to update a table in SQL2
with data from SQL1, I would create a link server on SQL2 that points to SQL1.
Then a scheduled job can do operations on both servers, e.g.

INSERT INTO sql2.mydb.dbo.mytable
SELECT * from sql1.mydb.dbo.mytable
WHERE ....

But, seriously, if you need to update the target server on a regular basis,
use replication. That's what it's there for.