SQL Server Performance Forum – Threads Archive

Query – new userIds in a messageTable

Hello! I have problems with a query that I am running. What I want to do is this: 1. Merge two user tables (from two different databases) to one user table in a new database.
2. Import all messages referencing to these users in those two tables to a new table in the new database. This is the way I do it right now. 1. Import all users. Store the old userId in a temporary field in the new table. – This works fine.
2. Loop through the old messagetable, and for each row, switch to the new DB and insert the new row using the new userId. The one above will work, but the problem is that it has been running for 12 hours and its not finished yet. It takes so much time since I have to fetch the new fromUserId, and toUserId for every row. Even if the id is indexed and it goes very well it just can do about 2000 records per seconds, and since my messagetable have million of rows, it wont work in the end. I tried one method to import all messages to the new table using the old userId, and then it imported everything "really" quick. Took about 1Â½ hour. After that I thought I could update the messages User Ids with the new one, doing something like: update message set fromUserId = (select top 1 id from users where oldUserId = fromUserId) And it works pretty well (fast and such), except that it will fail when it comes to that it replaces one userId with the new one, and a user alredy has the new userId. So when it comes to that userId it will replace booth the old user and the new user. Anyone?

Do u have all the databases on different servers or on the same server.
If all the DBs are on same server then its possible to communicate easily without importing the data.
for eg..
insert into db1..table1
select userid from db2..table1

I dont think that will help me in this case. That is because I need to split the current table into two tables. So I need to first copy the data into the messageData table, and then I need to create two rows in the other table. One for the sending user and one for the recieving. And I still need to convert the old userId to the new one.

I solved it by using your suggestion after all. What I did was that I first import all the messages into the new messagesData table. But along with the messagesData I also insert the old messageId. Then I insert all the messages into the other table, by joining the old database (messageData-table) with the new one. It processed everything in 8 minutes now <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks!

]]>