sql table replicated or triggers

pmtolk1
pmtolk1 used Ask the Experts™
on
We have 2 main tables on two different servers
Table A is the primary Table on primary Server
Table B is a replication of the primary Table on Secondary Server
Need
Table C [1 day of Table B's Data]  (On same server and database-secondary server)

I want to know how/what is the best method
for creating a third table which contains only 1 day worth of Data
I will write a script which deletes all data from table C for old data
that runs every night.

How do I get the data into Table C?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
Why do you need the data on the replicated server ?

INSERT INTO urTable
SELECT * from MasteTable
WHERE DateField >=CONVERT(varchar,  GetDate()-1, 112)


--to delete the master table ,use this if you dont have any relations

TRUNCATE TABLE masterTable

Author

Commented:
we need the data on the replicated server for running reports

I wrote a trigger which does an insert  currently and then a delete at night via a scheduled task
I'm not even sure why you need a table.

Why not create a view for the current day?  Unless you need to change the data and want it as a separate set of data not to impact the real data.

Think of using a view.

Otherwise, you can schedule a task to truncate the table and re-insert new data for "table c"
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
hmmm a view ... maybe ...

When I run certain commands they take forever because the I cant use the indexes of the table and
the queries need to do a table scan to get the data I need

The table has several million rows so I thought if I kept only 1 day worth of data then it will make
those reports faster.

If I make a view it will need to query the main table and filer based on a table scan for dates, yes?


I need the most optimized solution possible because of the big data set

Thanks for the ideas guys
Maybe you should optimize a table.

I have a table that has over 500 million rows.....

You need to avoid Table Scans.  They are VERY VERY bad.

You can also index a view.

Author

Commented:
2 questions then

1 in order to make the view aren't I doing a table scan on the date range, and isn't this occurring
every time the view gets called

2 how can you avoid table scans if you need to filter on an item that is not an index
like a phone number which has multiple entries per day?

Doesn't putting the data into tables that have the data for 1 day 7 days 40 days and all days solve these problems?

Author

Commented:
fixed the problem by adding more indexes to the table
AneeshDatabase Consultant
Top Expert 2009

Commented:
I would rather suggest partition the table instead of a view

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial