Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Create Table Event in SQL Server 2008 R2

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
htamraz1
Asked:
htamraz1
  • 2
  • 2
  • 2
2 Solutions
 
Ross TurnerManagement Information Support AnalystCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
htamraz1Director of TechnologyAuthor Commented:
Thanks RossTurner and aneeshattingal

aneeshattingal - can you explain the syntax 'where 1 =0'
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
Ross TurnerManagement Information Support AnalystCommented:
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
 
htamraz1Director of TechnologyAuthor Commented:
both expert comments offered a slightly different way of getting the job done, but were very helpful and to the point. Excellent job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now