Hi I have huge table which is coming from network in chunks. Rows are not ordered so can be duplicates. I need to keep track of duplicates and preserve all rows so that in future I can return rows as and when it came. Is there any framework or algorithms that help me achive this task? I would like to do versioning for all the rows so that I can go back in time and get rows for particular time date etc.

Please guide. How should I approach? I am new to Database. I need to implement this in Java. Thanks in advance.

asked 22 Jul '13, 12:02

user1070's gravatar image

user1070
70448
accept rate: 0%

edited 22 Jul '13, 12:09

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


Add an extra column to the table...

   input_order BIGINT NOT NULL DEFAULT AUTOINCREMENT,

Load the input data into the other columns, but let this column default by leaving it out of the column list being inserted. You can do this (leave a column out) when using either the INSERT or LOAD TABLE statements.

Then, input_order will be assigned the values 1, 2, 3 according to the order the rows were inserted.

This column WILL be unique, so you can make it the primary key if you want.

permanent link

answered 22 Jul '13, 16:09

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

1

You say, you would like to select rows for a particular date afterwards. To simplify that, you could additionally add a column to track the date/time when the row was inserted automatically:

input_time datetime NOT NULL DEFAULT CURRENT TIMESTAMP,

The DEFAULT CURRENT TIMESTAMP (or CURRENT UTC TIMESTAMP, if you prefer a consistent time reference) will be filled automatically with the current date/time.

Just as with the column Breck has suggested, let out this coulmn in the INSERT/LOAD TABLE statements.

(23 Jul '13, 06:51) Volker Barth

Hi @Breck thank a lot for the answer. How do I achieve this versioning using JDBC? I am uploading data into batches using addBatch() method.

(23 Jul '13, 12:23) user1070
Replies hidden
3

e.g. If you have a table:

create table t1(
    c1 integer,
    c2 varchar(32),
    input_order integer not null default autoincrement,
    input_time datetime not null default current timestamp
);

Then your PreparedStatement JDBC code would look like:

PreparedStatement ps = conn.prepareStatement("INSERT INTO t1 (c1, c2) VALUES (?, ?)";
ps.setInt(1,1);
ps.setString(2,"Hello");
ps.addBatch();
ps.executeBatch();

The key importance here is specifying the columns to be filled in the INSERT statement ((c1, c2)), while letting the other columns use the default values.

(23 Jul '13, 13:23) Jeff Albion
Your answer
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:

×65
×1

question asked: 22 Jul '13, 12:02

question was seen: 644 times

last updated: 23 Jul '13, 13:25