8 replies to this topic

[nomaden]

I have table on a database that contain one to many relationship with the following structure

user_id | last_login (date_time)

and I want to migrate all same user_id into new table with same structure and one to one relationship. So one user only have one last_login column.

the last_login now freetext / nvarchar(MAX). The new last_login column will be appended with new date each user_login.

user_id | last_login (nvarchar(max))

Is there way to conversion using SQL itself? or do I need some software preferably free to convert data from many row into single row.

[LuthfiHakim]

Yes you can do the conversion using SQL. But since you have to query multiple rows to combine into one value, you have to do it inside a stored procedure.

First you must decide the way to separate each date inside the new (varchar based) last_login field. You can choose to go with fix length of char, or using delimiter (e.g. comma, semicolon, or pipe).

Then create a stored procedure. Inside, it queries user table. And for each user it queries last_login table. At the start of querying a user's last login table (use cursor, forward only kind is the best), reset (empty) an nvarchar(max) variable. Now add (after converting into nvarchar) each last_login value you get into this variable. When reach the end of the cursor save that variable to your new last_login table.

[Orjan]

Why do it that way? I would do a REPLACE INTO for each data ordered like ORDER BY last_login ASC, that way, it's overwritten each time a new post is done on the same user_id. this requires that user_id is the only key field though...

opps, REPLACE INTO is apparently MySQL only... well, I found a way to do it another way here

so, try to update each row, if update didn't do it, insert it..

wouldn't this do it without any troubles?

as you perform it in the order of last_login, with oldest first, you will at last get all done, having the newest left, all the old ones overwritten.

[LuthfiHakim]

^ I believe now he wants to keep login time history inside a field for each user only. Quite good approach if such information never queried real time. And my suggestion was to address conversion of existing login times to new scheme, not about adding new ones.

[Orjan]

Um, a huge text field containing all login times? well, it contradicts everything about normal forms in database design... I believe it's an awful solution to do it that way, but ok...

[nomaden]

Thanks guys,

Yes, I am really aware about database design. It's contradict the database design though. :). I have minimum experience in data conversion so it's quite hard to figure.

The purpose was to create login time archiving. Seems crazy but user wants it so badly. This new table will be use for archiving purpose, it need store procedure to copy real time table into archiving table.

it's expected like this

User 1 | 10/10/2010
User 1 | 11/10/2010

to

User 1 | 10/10/2010 <newline> 11/10/2010

I will try to use store procedure.

[Orjan]

Is your client going into the database and look? Wouldn't it be better to have a normal log and have a back-end presentation in your software for the client to see these things instead?

[nomaden]

Unfortunately, they check it out. They look into the database wheter the table just like they want, i've been explained to them about table design practice also. After long and exhausted discussion with them, they resist to use the unnormalize table. So I just do what they want even I know it's awful table design.

Data was succesfully converted using SQL Server cursors using lutfi's algorithm.

[LuthfiHakim]

^ Don't worry about "denormalization". It's actually a natural process for some situations. Like for example keeping history (which seldom used in real time), or for speed thing up (to avoid query to another table).

Glad that I can help, cheers!