How to copy data from view and create new table in sql server database?

Question

Suppose I have to two tables, Employees and Departments - both are linked via pk and fk.

I have created a view myview and I want to trigger an event at 12:00 Am and create a new table and copy data from the view into new table.

There are two question here. 1. What is the sql to do this? 2. How do I automate this so it happens every midnight? Which are you asking? Or both? — Karl Kieninger, Oct 20 '15 at 20:45
you should check `SQL Server Agent job` to schedule something you want to happen at certain time — Juan Carlos Oropeza, Oct 20 '15 at 20:47

score 3 · Answer 1 · answered Oct 20 '15 at 20:46

3

Assuming there is no table already, you can query directly into the table from the view with this:

SELECT * 
INTO #NEWTABLE
FROM YOURVIEW

answered Oct 20 '15 at 20:46

dfundako

score 0 · Answer 2 · answered Oct 20 '15 at 20:45

0

I believe you can do the following:

CREATE TABLE <table_name> AS ( 
    SELEC * 
    FROM myview 
)

answered Oct 20 '15 at 20:45

CAllen

No, not in SQL Server - this is invalid T-SQL syntax ... – marc_s Oct 21 '15 at 05:04
Oh ok then. I'm sorry, I don't know how but I never saw SQL server in the above subject i just saw SQL. the query above by @dfundako should do it though. – CAllen Oct 21 '15 at 05:09

2 Answers2