Auto fill table from a View

• Sqlnew18

  SSC Rookie

  Points: 28

  More actions

  January 30, 2018 at 6:50 am

  #333060

  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

  SSC Guru

  Points: 246695

  More actions

  January 30, 2018 at 6:57 am

  #1977846

  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.

  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.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734459

  More actions

  January 30, 2018 at 8:54 am

  #1977868

  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.

• Sqlnew18

  SSC Rookie

  Points: 28

  More actions

  January 30, 2018 at 10:24 am

  #1977906

  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

  SSC Guru

  Points: 246695

  More actions

  January 30, 2018 at 11:28 am

  #1977921

  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.

• Sqlnew18

  SSC Rookie

  Points: 28

  More actions

  January 30, 2018 at 11:55 am

  #1977929

  Thank you very much for your help

  Ben