Job

Question

Post reply

Job

zistanbuli

Valued Member

Points: 53
More actions
August 4, 2009 at 1:17 am

#135049

hii
i hope some one could help me ... 🙂
i need to create a job in SQL server 2005 to pull data from a certain tables of database to another database on the same server... and i need this job to be scheduled every 1 hr.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

Welcome to the forum!

There are a few ways in which you could accomplish this:

1. A job that executes a stored procedure or T-SQL statements to copy/move the data.

2. A custom SSIS package that is scheduled to run every hour.

3. Snapshot replication scheduled to run every hour.

4. Using the Export/Import wizard and choosing to save the package that the wizard creates.

Have you already decided what method you want to use and just need help in creating a scheduled job?

Regards

Liam

zistanbuli Valued Member Points: 53 More actions · Answer 2

well thx for your reply 😉

i want to use the transact sql but i want the syntax so i can copy the data from one db to another then to schedule the job

Lempster SSCoach Points: 15651 More actions · Answer 3

Ok, then you'll want to do something like:

SELECT * INTO databaseB.dbo.table FROM database.dbo.table

assuming that the table in databaseB does not already exist. If it already exists then you can use:

INSERT INTO databaseB.dbo.table SELECT * FROM databaseA.dbo.table

If you only want to select a subset of columns rather than all of the columns then replace '*' with a comma-separated list of columns in the first query. You'll need to modify the second query like this:

INSERT INTO databaseB.dbo.table (col1, col2,...coln) SELECT col1, col2,....coln FROM databaseA.dbo.table

When you create the job make sure that the Type for the job step is set to 'Transact-SQL script (T-SQL)'

Thess are just the basic T-SQL commands; I would also check for the existence of the table (if using the second option), delete (or truncate) the existing data if everything is being copied from one table to another, incorporate some basic error handling etc.

Have a look in BOL (Books Online) for the full syntax of SELECT and INSERT.

Regards

Lempster

zistanbuli Valued Member Points: 53 More actions · Answer 4

well thanx am gonna try it

but no i only created the 2nd DB i still didnot build any tables in it.??

zistanbuli Valued Member Points: 53 More actions · Answer 5

well i have created a table just the same as the on in Database A and i used the second query you listed

INSERT INTO Noor.dbo.NTasks SELECT * FROM 706.dbo.GVTDPTASKS

but it gives me an error that the Database A name is incorrect

Error is :

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '706.'.

Lempster SSCoach Points: 15651 More actions · Answer 6

Because the name of your database in numeric, you'll need to wrap it in square brackets:

INSERT INTO Noor.dbo.NTasks SELECT * FROM [706].dbo.GVTDPTASKS

zistanbuli Valued Member Points: 53 More actions · Answer 7

hehe

yah i noticed that...

and i did this statement in sql query after i built the tables

thank you