I am using an intermediate table (Table2) to import data rows from another database, and then I compare this Table2 with my main Table (Table1), and then update only the rows in which there is a difference.

The import takes place every 2 hours, with each about 2 Million rows, which results at the end of the day very big (unnecessary) transaction log files!

Is there a way to instruct the database not to log the transactions on Table2? I am using also DBREMOTE, but only Table1 is in this case relevant, because Table2 is only intermediate (so no problem if the transactions of Table2 are ignored).

asked 11 Feb '20, 05:12

Baron's gravatar image

Baron
1.4k99110131
accept rate: 47%

1

There is a NOT TRANSNATIONAL clause available when creating global temporary tables, but not for conventional tables. It would be very hard for the backup / restore mechanism to work if potentially some of the data / transactions were not available - don't forget a transaction can impact on more than one table at a time.

(11 Feb '20, 05:55) Justin Willey
Replies hidden
2

NOT TRANSNATIONAL

@Justin, Still "brexitized"? Over here, it's NOT TRANSACTIONAL.

If Table2 is only meant to help to update the permanent table Table1, I see no real problem () in making Table2 a temporary table with NOT TRANSACTIONAL - the transaction on Table1 still is logged *and as such reliable for both restoring and also for SQL Remote. FWIW, I regularly use local temporary tables NOT TRANSACTIONAL with remote data and use that to update permanent tables.

(*) - under the assumption that the data for Table2 can reliably refetched from the other database lateron, i.e. is not "temporary data" there.

(11 Feb '20, 06:17) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×53
×47

question asked: 11 Feb '20, 05:12

question was seen: 332 times

last updated: 11 Feb '20, 07:43