January 30, 2018 at 6:50 am
Hi,
I need some help with a project we are working on.
I have a table containing 3 columns as followed:
1. ID (auto number)
2. Date (defaults to current date)
3. User ID (foreign key from another table)
I need this table to have the data entered into it automatically from a view that is run once a week (the view will pick one user ID at random).
I hope this all makes sense
Thanks in advance
Ben
January 30, 2018 at 6:57 am
davey3389 - Tuesday, January 30, 2018 6:50 AMHi,I need some help with a project we are working on.
I have a table containing 3 columns as followed:
1. ID (auto number)
2. Date (defaults to current date)
3. User ID (foreign key from another table)I need this table to have the data entered into it automatically from a view that is run once a week (the view will pick one user ID at random).
I hope this all makes sense
Thanks in advance
Ben
I suggest that you write a stored procedure to do this for you and then schedule that to run as often as is required.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2018 at 8:54 am
Views aren't run. views are essentially a query definition that a user can use in place of a table and the optimizer will then run the query as though it were inline.
As Phil said, you'd be better making this a stored procedure that takes an id as a parameter.
January 30, 2018 at 10:24 am
Thanks for the reply.
Here is what I have got working so farCREATE PROCEDURE [pr_Prize_Winner]
AS
SELECT TOP 1
u.[User_ID], u.[first_name], u.[last_name]
FROM [User] u, [Issue] i
WHERE u.[User_ID]=i.[Reporter_ID]
AND i.[Date_Submitted] BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
AND u.[HRD_Staff]='0'
ORDER BY NEWID()
So how do I edit this to store the result generated in the User_ID field into a table containing the 3 columns listed above.
Many thanks,
Ben
January 30, 2018 at 11:28 am
This sort of thing should work:CREATE PROCEDURE pr_Prize_Winner
AS
SET NOCOUNT ON;
DECLARE @User_Id INT = (
SELECT TOP 1
u.User_ID
FROM
[User] u
JOIN Issue i ON u.User_ID = i.Reporter_ID
WHERE
i.Date_Submitted BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
AND u.HRD_Staff = '0'
ORDER BY NEWID()
);
INSERT TargetTable(User_Id, First_Name, Last_Name)
SELECT User_Id, First_Name, Last_Name
FROM ...
WHERE User_Id = @User_Id
Notes
1) It is best practice to schema-qualify your table and proc names.
2) Using reserved words as column names (User_Id) is not recommended
3) You may want to issue a command to TRUNCATE the target table before running the INSERT.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2018 at 11:55 am
Thank you very much for your help
Ben
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply