Using timestamp delta to extract data in SQL

The most recent project requires an incremental extraction of the data from the millions, so we have a look at the application of the timestamp and hereby record

Timestamp-timestamp: A unique binary number automatically generated in a database that is independent of time and date and is typically used as a mechanism for adding a version stamp to a table row. Storage size is 8 bytes

Each database has a counter that is incremented when an INSERT or update operation is performed on a table that contains timestamp columns in the database. The counter is a database timestamp. This can track the relative time within the database, not the actual time associated with the clock. A table can have only one timestamp column. The incremental database timestamp value is inserted in the timestamp column each time the row that contains the timestamp column is modified or inserted. This property makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key. Any update to the row changes the timestamp value, thereby changing the key value. If the column belongs to a primary key, the old key value will be invalid, and the foreign key referencing the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the row in the cursor. If the column belongs to an index key, all updates to the data row will also cause the index to update.

Using the timestamp column in a row makes it easy to determine whether any value in the row has changed since the last time it was read. If a change is made to the row, the timestamp value is updated. If no changes are made to the row, the timestamp value is consistent with the timestamp value of the row that was previously read.

Usage Record:

Existing source table tmp_a and target table tmp_b, you need to synchronize the modified data in tmp_a to tmp_b every day

--Create Source Table Tmp_acreate table tmp_a (a varchar (ten), B TIMESTAMP) insert into tmp_a (a) VALUES (' abc ') insert into tmp_a (a) VALUES (' 1 23 ')--Create target Table Tmp_bcreate table Tmp_b (a varchar (ten), B TIMESTAMP)

Querying tmp_a and Tmp_b data

--Query Tmp_aselect * FROM Tmp_aselect * from Tmp_b

As a result, the maximum value is 0x00000000000084fd

Tmp_a data into the Tmp_b

--Import Table Tmp_binsert into Tmp_b (A, B) SELECT A, b from tmp_a

After execution, you will find that the error is reported:

MSG 273, Level 16, State 1, line 2nd
You cannot insert an explicit value into a timestamp column. Use Insert for the column list to exclude the timestamp column, or insert DEFAULT into the timestamp column.

Query the information after that: timestamp This value is generally the database is automatically added and modified, equivalent to the automatic growth of the identity (and the implementation of the update modification statement This field will be automatically updated), so the general field we only do query operations. If you want to update this field, you will be prompted with this error message: The timestamp column cannot be updated. However, this field can be added manually, but you can only use the Defalut field, which is a default value for the SQL Service database, and if you pass in another value, you will be prompted with an error message: You cannot insert an explicit value into a timestamp column. Use Insert for the column list to exclude timestamp columns, or insert DEFAULT into a timestamp column

So the timestamp data needs to be transformed

The structure of the modified Tmp_b is:

--Create a target table Tmp_bcreate table Tmp_b (a varchar (8), B VARBINARY)

Then convert the value of column B in tmp_a to a hexadecimal string

--Import Table Tmp_binsert into Tmp_b (A, B) SELECT A,cast (as VARBINARY (8)) from tmp_a

Execution succeeds so that the tmp_a and tmp_b data are the same

Modifying the data in Tmp_a, you will find that column B in tmp_a has been modified with column value ' abc '

UPDATE tmp_aset a= ' def ' WHERE a= ' abc ' SELECT * from tmp_a--results abdef0x00000000000085021230x0000000000008501

Compare two table data with different B-column data, then update the operation

SELECT * from tmp_a ta WHERE ta.b>0x00000000000084fd

　　

Using timestamp delta to extract data in SQL