SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auto fill table from a View


Auto fill table from a View

Author
Message
davey3389
davey3389
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208010 Visits: 24242
davey3389 - Tuesday, January 30, 2018 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

I suggest that you write a stored procedure to do this for you and then schedule that to run as often as is required.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)

Group: Administrators
Points: 585327 Visits: 20899
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
davey3389
davey3389
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
Thanks for the reply.

Here is what I have got working so far
CREATE 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

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208010 Visits: 24242
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
davey3389
davey3389
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
Thank you very much for your help

Ben
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search