Update Query containg static data and data from another table.

[holmm]

Hi,

First post so apologies if this sounds a bit confusing!!

I'm trying to run the following update. On a weekly basis i want to
insert all the active users ids from a users table into a timesheets
table along with the last day of the week and a submitted flag set to
0. I plan then on creating a schduled job so the script runs weekly.
The 3 queries i plan to use are below.

Insert statement:

INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS .USER_ID,
TBL_TIMESHEETS. WEEK_ENDING, TBL_TIMESHEETS. IS_SUBMITTED)
VALUES ('user ids', 'week end date', '0')

Get User Ids:

SELECT TBL_USERS.USER_ ID from TBL_USERS where TBL_USERS.IS_AC TIVE = '1'

Get last date of the week
SELECT DATEADD(wk, DATEDIFF(wk,0,g etdate()), 6)

I'm having trouble combing them as i'm pretty new to this. Is the best
approach to use a cursor?

If you need anymore info let me know. Thanks in advance.

[Hugo Kornelis]

Re: Update Query containg static data and data from another table.

On 28 Sep 2006 07:24:36 -0700, holmm wrote:

>Hi,
>
>First post so apologies if this sounds a bit confusing!!
>
>I'm trying to run the following update. On a weekly basis i want to
>insert all the active users ids from a users table into a timesheets
>table along with the last day of the week and a submitted flag set to
>0. I plan then on creating a schduled job so the script runs weekly.
>The 3 queries i plan to use are below.
>
>Insert statement:
>
>INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS .USER_ID,
>TBL_TIMESHEETS .WEEK_ENDING, TBL_TIMESHEETS. IS_SUBMITTED)
>VALUES ('user ids', 'week end date', '0')
>
>Get User Ids:
>
>SELECT TBL_USERS.USER_ ID from TBL_USERS where TBL_USERS.IS_AC TIVE = '1'
>
>Get last date of the week
>SELECT DATEADD(wk, DATEDIFF(wk,0,g etdate()), 6)
>
>I'm having trouble combing them as i'm pretty new to this. Is the best
>approach to use a cursor?
>
>If you need anymore info let me know. Thanks in advance.

Hi holmm,

Try if this gets you the desired results:

INSERT INTO TBL_TIMESHEETS
(TBL_TIMESHEETS .USER_ID, TBL_TIMESHEETS. WEEK_ENDING,
TBL_TIMESHEETS. IS_SUBMITTED)
SELECT TBL_USERS.USER_ ID, DATEADD(wk, DATEDIFF(wk,0,g etdate()), 6), '0'
FROM TBL_USERS
WHERE TBL_USERS.IS_AC TIVE = '1';

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[ZeldorBlat]

Re: Update Query containg static data and data from another table.


holmm wrote:

Hi,
>
First post so apologies if this sounds a bit confusing!!
>
I'm trying to run the following update. On a weekly basis i want to
insert all the active users ids from a users table into a timesheets
table along with the last day of the week and a submitted flag set to
0. I plan then on creating a schduled job so the script runs weekly.
The 3 queries i plan to use are below.
>
Insert statement:
>
INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS .USER_ID,
TBL_TIMESHEETS. WEEK_ENDING, TBL_TIMESHEETS. IS_SUBMITTED)
VALUES ('user ids', 'week end date', '0')
>
Get User Ids:
>
SELECT TBL_USERS.USER_ ID from TBL_USERS where TBL_USERS.IS_AC TIVE = '1'
>
Get last date of the week
SELECT DATEADD(wk, DATEDIFF(wk,0,g etdate()), 6)
>
I'm having trouble combing them as i'm pretty new to this. Is the best
approach to use a cursor?
>
If you need anymore info let me know. Thanks in advance.

You've already got all the parts you need. Just stick 'em together :)

INSERT INTO TBL_TIMESHEETS (USER_ID, WEEK_ENDING, IS_SUBMITTED)
SELECT USER_ID, DATEADD(wk, DATEDIFF(wk, 0, getdate()), 6), 0
FROM TBL_USERS
WHERE IS_ACTIVE = '1'

[Ed Murphy]

Re: Update Query containg static data and data from another table.

holmm wrote:

Is the best approach to use a cursor?

Almost never.

[holmm]

Re: Update Query containg static data and data from another table.


Ed Murphy wrote:

holmm wrote:
>

Is the best approach to use a cursor?

>
Almost never.

Thanks for the replys. I'll go for the simply insert statement rather
than a cursor.