![]() ![]() The OldID and OldAccountID columns are of type VARCHAR(40). INNER JOIN `AccountMaster` AS B ON A.OldAccountID=B.OldIDĪny idea how do I mitigate this issue? I'm pretty sure that the 36 character OldID is creating issues after migration even though all the columns 'ID' & 'OldID' in AccountMaster as well as 'ID' & 'OldAccountID' in Transactions in MySQL are indexed. The issue now is that when I run a query like below to update the Transactions table with the new IDs, it takes hours and never really completes. So, I manage to migrate all 45K accounts in my AccountMaster as well as 1M transactions in my Transactions table while maintaining the old ID (UNIQUEINDENTIFIER) as OldID in both AccountMaster and Transactions.īelow is the scenario after migration in my MySQL database AccountMaster In my tables, I have migrated the AccountMaster ID as OldID and I'm creating new ID which are of type INT (Auto Increment). In the older application AccountMaster has ID as UNIQUEIDENTIFIER and Transactions table references it. ![]() ![]() The issue is with 2 tables particularly as below.ĪccountMaster which has approx 45,000 accounts and Transactions which has approx 1M rows. ![]() In my new application that I'm developing using PHP & MySQL, I wrote the script to migrate all the data from MS-SQL to MySQL. The earlier developer used UNIQUEIDENTIFIER as the primary key for all tables that needed one. To query more records radomly, increase the TOP value.My client's old application is developed in. The following example queries a random record from the Production.Product table using the NEWID() function. SELECT Query random data with the NEWID() function Then, the variable is assigned a value by using the SET statement. The following example declares a local variable called as a variable of uniqueidentifier data type. Using uniqueidentifier and variable assignment ,('Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL, ,('Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL, ,('Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL, ,('Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP', ('Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL, PostalCode, CountryRegion, Telephone, Fax) (Company, ContactName, Address, City, StateProvince, Creating a table using NEWID for uniqueidentifier data type. In assigning the default value of NEWID(), each new and existing row has a unique value for the CustomerID column. The following example creates the cust table with a uniqueidentifier data type, and uses NEWID to fill the table with a default value. This number is shown only for illustration. The term GUID stands for Globally Unique Identifier and it is used interchangeably with UNIQUEIDENTIFIER. The value returned by NEWID is different for each computer. What is a GUID GUID is a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |