2

I have a SQL Server view vwSales which returns all sales from yesterday. I want to dump the result into an actual table (tblSales) which needs to be recreated daily.

How to implement it by creating a script and automate the running thru SQL Server Agent?

This is the logic:

  • If tblSales exist then delete (for data refresh)

    Select * 
from vwSales

Insert to tblSales (to be recreated from the result of vwSales)

The job will run once a day before start of business hours.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dion
  • 59
  • 5
  • Why do you need to "recreate" it daily ? You can do a `delete tblSales` before `insert` – Squirrel Feb 13 '23 at 00:38
  • the tblSales is just a dump table that needs to be recreated daily, and will be the source of some adhoc reports. – Dion Feb 13 '23 at 00:52

2 Answers2

1

You can check for the existence of a table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tblSales')
BEGIN
    PRINT 'Table Exists'
END

and you can insert-select values, like this:

INSERT INTO destination_table(...)
SELECT ...
FROM source_table
...

So, you will need to properly specify the fields in the insert clause and the select clause and put the insert-select into the if.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
1

This query will check that if the table exists, it will drop it. Assuming that you need to create the table every time as your table/view structure may change, you want to handle this by script.

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblSales' AND type = 'U')
DROP TABLE tblSales
GO

The query will create the table based on data returns by the View and insert the records as well.

select * into tblSales from vwSales
Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19