I am looking to migrate data from one of the legacy database to one of Liferay tables. I can write the migration script, but I was wondering if there will be some issue with "Counter" service of liferay.
For example I have legacy custom user table. I need to move the users inside this table to Liferay's User_ table. I can use sql script to move the data. I am wondering what happens with primary key. As far as I know Liferay has counter service to create primary key and keep track of current id.
So while migrating is there anything that I need to do so that the Counter is not messed up after migration.
There are more issues than just the counter. You should strictly use Liferay's API to import the content.
I could name a few potential issues to pay attention to, but this will probably miss a few more - however it'd make you (or others reading this answer) confident that they now can cope with all issues. You can't. Don't go there. Simply use the API and it will take care of updating all the required dependencies. Plus, the API makes obvious what other data you still need in conjunction with your imported data.
Anybody who describes with more detail will set you up for disaster. And we've seen people who discovered their disasters more than 6 months after their manual writing to the database (in a particular case with running into duplicate primary keys, e.g. specifically the counter that you mention). Any of the possible failures might bring down your portal from one second to the other when you've long forgotten that you (or someone else) manually wrote to the database.