Scheduled Query ??????? - 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 2005 Forums

Transact-SQL (2005)

Scheduled Query ???????

Author

Topic

abudahim
Starting Member

4 Posts

Posted - 2008-04-20 : 12:27:48

hi everyone
i want to execute a sql insert statement everyday at 7 AM .
how to do this ?

here is the query

insert into attendance
{
date,
accountId,
clockIn,
clockOut
}

values
{
now, // current date
accountId, // employee id
null,
null
}

im beginner in sql server, so please elaborate with examples
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-20 : 13:45:28

You just need to create a procedure with the current insert statement as

CREATE PROC YourProc
@AccountID int
AS

insert into attendance
{
date,
accountId,
clockIn,
clockOut
}

values
{
getdate(), // current date
@AccountID, // employee id
null,
null
}

GO

Then use a SQL Server agent job to schedule this daily at your time. The scheduler will execute this procedure as per the schedule and perform the insert.

Refer to this link to know more on scheduling:-

http://msdn2.microsoft.com/en-us/library/aa176968.aspx

abudahim
Starting Member

4 Posts

Posted - 2008-04-20 : 15:26:07

hi visakh16
i don't want to use stored procedure. therefore i created an agent job and put the script inside the job.

here is the script

insert into attendance
(
accountId,
date,
clockIn,
clockOut
)

values
(
null, // here is my problem
GETDATE(),
null,
null
)

my question is, how i can insert a row for each accountId available in the account table ?
i.e :

for each accountId
insert new row
End For

thank you

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 15:41:06

[code]insert attendance (accountId, date, clockIn, clockOut)
select accountId, GETDATE(), null, null from account[/code]
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

abudahim
Starting Member

4 Posts

Posted - 2008-04-20 : 17:33:27

hi Ryan
i tried it, it didn't work.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 18:01:42

So... what happened?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 00:20:52