?
Solved

Create Table Event in SQL Server 2008 R2

Posted on 2013-05-29
6
Medium Priority
?
425 Views
Last Modified: 2013-05-30
I am an Access user and getting to know SQL server 2008 R2.

I have a SQL view 'dbo.Grantee_Partner'
I would like to creae a table called 'gifts' based on this view that is refreshed (i.e. overwritten) daily at 6 am based on the view 'dbo.Grantee_Partner'

What is the easiest way to accomplish this in SQL Server?
Please point me to specific examples if possible.

Thank you,
0
Comment
Question by:htamraz1
  • 2
  • 2
  • 2
6 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39205069
I would create a sql agent job to run a script like below

SELECT *
INTO gifts
FROM dbo.Grantee_Partner

Open in new window


going to hunt out a good sql agent script for you
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 39205070
step1: create the table
           SELECT * INTO dbo.Gifts FROM Grantee_partner where 1 =0
step2. create a sql server agent Job with the following statement  and schedule it to run daily at 6 am
          TRUNCATE TABLE dbo.Gifts
          INSERT INTO dbo.Gifts SELECT * from dbo.Grantee_Partner
0
 

Author Comment

by:htamraz1
ID: 39205150
Thanks RossTurner and aneeshattingal

aneeshattingal - can you explain the syntax 'where 1 =0'
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39205158
>can you explain the syntax 'where 1 =0'
That will just create the table structure from the view; since '1=0' is always false, it wont populate any data
0
 
LVL 7

Accepted Solution

by:
Ross Turner earned 1000 total points
ID: 39205163
Hi  htamraz1

I've created the code below which will create a sql agent job named
Create Table Gifts

i used
http://technet.microsoft.com/en-us/library/ms190268.aspx

The sql that it will intiate is found under the @command
IF OBJECT_ID(''dbo.Gifts'', ''U'') IS NOT NULL
 TRUNCATE TABLE dbo.Gifts
 INSERT INTO dbo.Gifts SELECT * from dbo.Grantee_Partner

Open in new window

pic1CODE THAT WILL CREATE THE JOB
USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Create Table Gifts' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Create Table Gifts',
    @step_name = N'Truncate and Generate Table Gifts',
    @subsystem = N'TSQL',
    @command = N'IF OBJECT_ID(''dbo.Gifts'', ''U'') IS NOT NULL
 TRUNCATE TABLE dbo.Gifts
 INSERT INTO dbo.Gifts SELECT * from dbo.Grantee_Partner', 
    @retry_attempts = 0,
    @retry_interval = 0 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'Generate Table Gifts',
	
    @freq_type = 4,
	@freq_interval=1, 
    @active_start_time = 060000 ;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Create Table Gifts',

   @schedule_name = N'Generate Table Gifts';

GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Create Table Gifts';
GO

Open in new window

0
 

Author Closing Comment

by:htamraz1
ID: 39208163
both expert comments offered a slightly different way of getting the job done, but were very helpful and to the point. Excellent job!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question