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: 246965

  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.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  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: 246965

  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.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Sqlnew18

  SSC Rookie

  Points: 28

  More actions

  January 30, 2018 at 11:55 am

  #1977929

  Thank you very much for your help

  Ben