Erwthsh gia SQL Server - Migration se MySQL

Συζητήσεις για τον Microsoft SQL Server

Συντονιστές: WebDev Moderators, Super-Moderators

Απάντηση
Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 13 Ιουν 2011 19:47

Hello!

Exw ena mikro 8emataki kai 8elw na rwthsw sxetika me thn SQL server 2008.

8elw na kanw ena migration gia na perasw dedomena se MySQL alla exw kapoia pedia pou
einai dipla kai tripla kai den exw brei ena kolhma giati 8elw na kanw "match" oles tis epanalhspeis sto ms sql se ena neo koino Id sthn mysql!

Yparxei kapoio paradeigma se script gia na dw?


Sas euxaristw prokatabolika gia th bohtheia.

Intefix
Δημοσιεύσεις: 187
Εγγραφή: 22 Σεπ 2007 16:21
Τοποθεσία: Ηράκλειο Κρήτης
Επικοινωνία:

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Intefix » 14 Ιουν 2011 15:47

Migration Tool της MySQL

pimpogio
Δημοσιεύσεις: 1080
Εγγραφή: 28 Δεκ 2010 14:08

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από pimpogio » 15 Ιουν 2011 02:06

θελω κ εγω κατι παρομιο απο mysql migrate σε postgresql
και απο mysql σε sqlite ?
yπαρχουνε τετοια tools ?

Άβαταρ μέλους
hitca
Honorary Member
Δημοσιεύσεις: 1919
Εγγραφή: 13 Ιουν 2010 19:41
Τοποθεσία: Brussels
Επικοινωνία:

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από hitca » 15 Ιουν 2011 12:04

Αυτό νομίζω μπορεί να βοηθήσει για database migration >> ESF Database Migration Toolkit

Υποστηρίζει :
Oracle, MySQL, SQL Server, PostgreSQL, IBM DB2, IBM Informix, InterSystems Caché, Teradata, Visual Foxpro, SQLite, FireBird, InterBase, Microsoft Access, Microsoft Excel, Paradox, Lotus, dBase, CSV/Text
«Μάθε από τα λάθη των άλλων γιατί δε θα προλάβεις να τα κάνεις όλα μόνος σου»
Οι Έλληνες είμαστε «θεατές των λόγων και ακροατές των έργων» (.... ο Θουκυδίδης το είπε !)
«Υπάρχουν τριών ειδών άνθρωποι. Αυτοί που κερδίζουν, αυτοί που χάνουν και αυτοί που καθορίζουν ποιοι κερδίζουν και ποιοι χάνουν! »

Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 15 Ιουν 2011 21:00

Euxaristw alla 8elw kati custom se script, giati de me boleuei kanena ergaleio apo auta.

Άβαταρ μέλους
Pavel
Honorary Member
Δημοσιεύσεις: 1046
Εγγραφή: 08 Αύγ 2003 00:05
Τοποθεσία: UK

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Pavel » 16 Ιουν 2011 01:40

Διόρθωσε πρώτα την βάση σου και ασχολήσου αργότερα με το migration.
Εσύ είσαι τρελός.

Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 16 Ιουν 2011 14:51

To problima pou exw einai oti de mporw na sbhsw eggrafes mias kai einai production bash.

To 8ema einai oti olh h domh einai lathos kai prepei na meinei ws exei kai egw aplws na metaferw se nea domh ola ta dedomena se mysql apo ms sql.

Ara einai monodromos. Alliws tha ta eixa allaksei ola apo thn arxh kai de 8a eixa problhma

Άβαταρ μέλους
fafos
Script Master
Δημοσιεύσεις: 6236
Εγγραφή: 30 Νοέμ 2004 03:09

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από fafos » 16 Ιουν 2011 19:27

einai polles oi eggrafes? ti enoeis me to "match"? tha krathseis tis eggrafes alla me diaforetiko id h tha diagrapseis tis diplotriples eggrafes?
Οι πάνες και οι πολιτικοί πρέπει να αλλάζονται συχνά για τον ίδιο λόγο...

Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 17 Ιουν 2011 10:48

milame gia xiliades eggrafes, diplotriples kai oles exoun diaforetiko shmeio pou "denoyn" alla 8elw na tous ana8esw koino ID wste na ta exw mia fora sth nea bash...
thelw diladi ena SQL script pou na kanei on-the-fly fix kai migrate...

kanena kolpo?

pimpogio
Δημοσιεύσεις: 1080
Εγγραφή: 28 Δεκ 2010 14:08

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από pimpogio » 20 Ιουν 2011 13:25

φτιαξε ενα προγραμματακι σε java/python/php (με μεγαλο timeout στο execution)
οτι ξερεις και τραβα απο την mssql και περνα τα στην mysql προσοχη αυτη τη φορα να κανεις normalization στην καινουργια βαση..

και προσοχη η νεα βαση να εχει innodb engine foreign/unique key constraints και να χρησιμοποιησεις transactions ωστε να κανεις σωστη μεταφορα χωρις προβληματα στη νεα βαση.

με myisam engine sthn mysql δεν μπορεις να το κανεις αυτο...

Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 20 Ιουν 2011 18:05

Ευχαριστώ πολύ παιδιά.

Βασικά με MyISAM δουλεύω για λόγους ταχύτητας και τα κατάφερα χωρις transactions και constraints.

Apla bghke to code ligo terastio!

Άβαταρ μέλους
korgr
Honorary Member
Δημοσιεύσεις: 5067
Εγγραφή: 07 Οκτ 2008 18:30
Τοποθεσία: Corinth
Επικοινωνία:

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από korgr » 20 Ιουν 2011 20:07

Ακούς pimpogio?
Το έκανε με myisam ο άνθρωπος :wink:

Άβαταρ μέλους
Rapid-eraser
WebDev Moderator
Δημοσιεύσεις: 6851
Εγγραφή: 05 Απρ 2003 17:50
Τοποθεσία: Πειραιάς
Επικοινωνία:

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Rapid-eraser » 20 Ιουν 2011 20:54

Ο Τσακ Νόρις ρωτάει τον pimpogio όταν θέλει να βρει τι storage engine να χρησιμοποιήσει :P
Cu, Rapid-eraser, Tα αγαθά copies κτώνται.
Love is like oxygen, You get too much you get too high
Not enough and you're gonna die, Love gets you high

Apostolis_38
Δημοσιεύσεις: 1969
Εγγραφή: 14 Φεβ 2008 16:20
Τοποθεσία: ΠΕΙΡΑΙΑΣ

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Apostolis_38 » 20 Ιουν 2011 21:39

:D :D :D :D

Ο Τσάκ Νόρις φωνάζει τον pimpogio να του κάνει normalization :hammer:

Grey-Hat
Δημοσιεύσεις: 7
Εγγραφή: 13 Ιουν 2011 19:40

Erwthsh gia SQL Server - Migration se MySQL

Δημοσίευση από Grey-Hat » 21 Ιουν 2011 11:39

Γειά σας.
Ακολουθεί ο κώδικας με τις δοκιμές που έκανα για να πετύχω το migration. Ελπίζω να βοηθήσει και άλλους αν αντιμετωπίσουν παρόμειο πρόβλημα.

Εγω μια φορά έφαγα ΦΡΙΚΗ! - Chuck Norris save me!!! ;-)

Κώδικας: Επιλογή όλων

/*

   <<Migration - &#40;MS SQL -> MySQL&#41;>>

*/



/* Drop previous linked server */
EXEC sp_dropserver @server = 'local_mysql'

/* Add new linked server */
EXEC sp_addlinkedserver @server = 'local_mysql', 
						@srvproduct = '', 
						@provstr = N'DRIVER=&#123;MySQL ODBC 5.1 Driver&#125;; 
								 	 SERVER=127.0.0.1; 
									 DATABASE=; 
									 USER=root; 
									 PASSWORD=mysql;', 
						@provider = N'MSDASQL'
GO



/* Create new temporary tables */
CREATE TABLE #MY_TEMP_CNR &#40;id INT IDENTITY&#40;1,1&#41; NOT NULL, 
						   label_id INT NOT NULL,
						   old_id INT NOT NULL,  
						   old_parent_id INT NOT NULL,
						   new_parent_id INT NOT NULL, 
						   sort INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_CNR_PARENTS &#40;old_id INT NOT NULL, 
								   new_parent_id INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_CNR_LANG &#40;new_lang_label_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
								old_lang_label_id INT NOT NULL, 
								label NVARCHAR&#40;255&#41; NOT NULL, 
								intl_label NVARCHAR&#40;255&#41;&#41;

CREATE TABLE #MY_TEMP_ESTATES &#40;id INT IDENTITY&#40;1,1&#41; NOT NULL, 
							   old_id INT NOT NULL, 
							   cat_id INT NOT NULL, 
							   reg_date DATETIME NOT NULL, 
							   descr_id INT NOT NULL, 
							   serial_number NVARCHAR&#40;20&#41; NOT NULL, 
							   location NVARCHAR&#40;100&#41; NOT NULL, 
							   price NVARCHAR&#40;22&#41; NOT NULL, 
							   price_max NVARCHAR&#40;22&#41; NULL, 
							   surface NVARCHAR&#40;12&#41; NOT NULL, 
							   surface_max NVARCHAR&#40;12&#41; NULL, 
							   &#91;address&#93; NVARCHAR&#40;70&#41; NOT NULL, 
							   zip NVARCHAR&#40;50&#41; NOT NULL, 
							   url NVARCHAR&#40;255&#41; NULL, 
							   notes NTEXT NULL, 
							   modification_id BIGINT NOT NULL, 
							   currency_id TINYINT NOT NULL, 
							   mu_id TINYINT NOT NULL, 
							   company_commission_amount NVARCHAR&#40;22&#41; NOT NULL, 
							   company_commission_percent NVARCHAR&#40;22&#41; NOT NULL, 
							   offer_commission_amount NVARCHAR&#40;22&#41; NOT NULL, 
							   offer_commission_percent NVARCHAR&#40;22&#41; NOT NULL, 
							   view_until_date DATETIME NOT NULL, 
							   sort_order INT NOT NULL, 
							   is_visible TINYINT NOT NULL, 
							   is_price_visible TINYINT NOT NULL, 
							   is_offer_commission_visible TINYINT NOT NULL, 
							   is_archived TINYINT NOT NULL, 
							   checked TINYINT NOT NULL, 
							   &#91;unique&#93; TINYINT NOT NULL, 
							   rating TINYINT NULL&#41;

CREATE TABLE #MY_TEMP_ESTATES_DESCR &#40;new_lang_descr_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
									 old_lang_descr_id INT NOT NULL, 
									 descr NTEXT NOT NULL, 
									 intl_descr NTEXT NOT NULL&#41;

CREATE TABLE #MY_TEMP_ESTATES_CAT &#40;id INT IDENTITY&#40;1,1&#41; NOT NULL, 
								   label_id INT NOT NULL,
								   old_id INT NOT NULL,  
								   old_parent_id INT NOT NULL,
								   new_parent_id INT NOT NULL, 
								   sort INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_ESTATES_CAT_PARENTS &#40;old_id INT NOT NULL, 
										   new_parent_id INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_ESTATES_CAT_LANG &#40;new_lang_label_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
										old_lang_label_id INT NOT NULL, 
										label NVARCHAR&#40;255&#41; NOT NULL, 
										intl_label NVARCHAR&#40;255&#41;&#41;

CREATE TABLE #MY_TEMP_USERS &#40;new_user_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
							 old_user_id INT NOT NULL, 
							 username NVARCHAR&#40;30&#41; NOT NULL, 
							 &#91;password&#93; NVARCHAR&#40;32&#41; NOT NULL, 
							 email NVARCHAR&#40;100&#41; NOT NULL&#41;

CREATE TABLE #MY_TEMP_USERS_OWN &#40;new_owner_id INT NOT NULL, 
								 old_owner_id INT NOT NULL, 
								 estate_id INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_USERS_CUS &#40;new_cus_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
								 old_cus_id INT NOT NULL, 
								 cat_id INT NOT NULL, 
								 new_manager_id INT NOT NULL, 
								 old_manager_id INT NOT NULL, 
								 new_country_id INT NOT NULL, 
								 old_country_id INT NOT NULL,  
								 company NVARCHAR&#40;100&#41; NULL, 
								 &#91;address&#93; NVARCHAR&#40;70&#41; NULL, 
								 zip NVARCHAR&#40;10&#41; NULL, 
								 occupation NVARCHAR&#40;70&#41; NULL, 
								 photo NVARCHAR&#40;255&#41; NULL, 
								 phone NVARCHAR&#40;50&#41; NULL, 
								 mobile NVARCHAR&#40;50&#41; NULL, 
								 fax NVARCHAR&#40;50&#41; NULL, 
								 email NVARCHAR&#40;100&#41; NULL, 
								 url NVARCHAR&#40;255&#41; NULL, 
								 notes NTEXT NULL&#41;

CREATE TABLE #MY_TEMP_USERS_CUS_CAT &#40;id INT IDENTITY&#40;1,1&#41; NOT NULL, 
									 label_id INT NOT NULL,
									 old_id INT NOT NULL,  
									 old_parent_id INT NOT NULL,
									 new_parent_id INT NOT NULL, 
									 sort INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_USERS_CUS_CAT_PARENTS &#40;old_id INT NOT NULL, 
											 new_parent_id INT NOT NULL&#41;

CREATE TABLE #MY_TEMP_USERS_CUS_CAT_LANG &#40;new_lang_label_id INT IDENTITY&#40;1,1&#41; NOT NULL, 
										  old_lang_label_id INT NOT NULL, 
										  label NVARCHAR&#40;255&#41; NOT NULL, 
										  intl_label NVARCHAR&#40;255&#41;&#41;

GO



/* Delete previous currencies &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41;

/* Delete previous measure units &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41;

/* Delete previous data from translations labels table &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.trans_labels'&#41;

/* Clean previous data from countries &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.countries'&#41;

/* Clean previous data from real estates descriptions &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.trans_real_estates_descr'&#41;

/* Clean previous data from real estates &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.real_estates'&#41;

/* Clean pevious data from real estates categories &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.real_estates_categories'&#41;

/* Clean previous data from users &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.users'&#41;

/* Clean previous data from ownerships &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.ownerships'&#41;

/* Clean previous data from customers &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.customers'&#41;

/* Clean pevious data from customers categories &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.customers_categories'&#41;

/* Clean pevious data from temp estates ID mapping &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.temp_estates_id_mapping'&#41;

/* Clean pevious data from temp users ID mapping &#91;MySQL&#93; */
DELETE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.temp_users_id_mapping'&#41;

GO



/* Insert new currencies &#91;MySQL&#93; */
INSERT OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SELECT id, 0, symbol, 1 FROM old_db.dbo.currencies

/* Insert new translation labels for currencies &#91;MySQL&#93; */
INSERT OPENQUERY&#40;local_mysql, 'SELECT label_id, label, lang_code FROM old_db.trans_labels'&#41; 
VALUES &#40;130301, 'Euro', 'en'&#41;, 
	   &#40;130301, '&#197;&#245;&#241;&#254;', 'gr'&#41;, 
	   &#40;130302, 'U.S Dollar', 'en'&#41;, 
	   &#40;130302, '&#193;&#236;&#229;&#241;&#233;&#234;&#220;&#237;&#233;&#234;&#239; &#196;&#239;&#235;&#235;&#220;&#241;&#233;&#239;', 'gr'&#41;,
	   &#40;130303, 'Canadian Dollar', 'en'&#41;, 
	   &#40;130303, '&#202;&#225;&#237;&#225;&#228;&#221;&#230;&#233;&#234;&#239; &#196;&#239;&#235;&#235;&#220;&#241;&#233;&#239;', 'gr'&#41;,
	   &#40;130304, 'Great Britain Pound', 'en'&#41;, 
	   &#40;130304, '&#203;&#223;&#241;&#225; &#193;&#227;&#227;&#235;&#223;&#225;&#242;', 'gr'&#41;,
	   &#40;130305, 'Australian Dollar', 'en'&#41;, 
	   &#40;130305, '&#193;&#245;&#243;&#244;&#241;&#225;&#235;&#233;&#225;&#237;&#252; &#196;&#239;&#235;&#235;&#220;&#241;&#233;&#239;', 'gr'&#41;

/* Update currencies labels &#91;MySQL&#93; */
UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SET label_id = 130301 
WHERE symbol = 'EUR'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SET label_id = 130302 
WHERE symbol = 'USD'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SET label_id = 130303 
WHERE symbol = 'CAD'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SET label_id = 130304 
WHERE symbol = 'GBP'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.currencies'&#41; 
SET label_id = 130305 
WHERE symbol = 'AUD'

/* Insert new measure units &#91;MySQL&#93; */
INSERT OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SELECT id, 0, Symbol, 1 FROM old_db.dbo.MeasureUnits

/* Insert new translation labels for measure_units &#91;MySQL&#93; */
INSERT OPENQUERY&#40;local_mysql, 'SELECT label_id, label, lang_code FROM old_db.trans_labels'&#41; 
VALUES &#40;130306, 'Meters', 'en'&#41;, 
	   &#40;130306, '&#204;&#221;&#244;&#241;&#225;', 'gr'&#41;, 
	   &#40;130307, 'Acres', 'en'&#41;, 
	   &#40;130307, '&#162;&#234;&#241;&#229;&#242;', 'gr'&#41;,
	   &#40;130308, 'Feets', 'en'&#41;, 
	   &#40;130308, '&#208;&#252;&#228;&#233;&#225;', 'gr'&#41;,
	   &#40;130309, 'Yards', 'en'&#41;, 
	   &#40;130309, '&#195;&#233;&#220;&#241;&#228;&#229;&#242;', 'gr'&#41;,
	   &#40;130310, 'Hectares', 'en'&#41;, 
	   &#40;130310, '&#197;&#234;&#244;&#220;&#241;&#233;&#225;', 'gr'&#41;,
	   &#40;130311, 'Miles', 'en'&#41;, 
	   &#40;130311, '&#204;&#223;&#235;&#233;&#225;', 'gr'&#41;,
	   &#40;130312, 'Per 1000 meters', 'en'&#41;, 
	   &#40;130312, '&#202;&#220;&#232;&#229; 1000 &#236;&#221;&#244;&#241;&#225;', 'gr'&#41;

/* Update measure units labels &#91;MySQL&#93; */
UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130306 
WHERE symbol = 'Meters²'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130307 
WHERE symbol = 'Acres'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130308 
WHERE symbol = 'Feets²'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130309 
WHERE symbol = 'Yards²'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130310 
WHERE symbol = 'Hectares'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130311 
WHERE symbol = 'Miles²'

UPDATE OPENQUERY&#40;local_mysql, 'SELECT * FROM old_db.measure_units'&#41; 
SET label_id = 130312 
WHERE symbol = '1000m²'

GO



/* ----- Greece ----- */
INSERT INTO #MY_TEMP_CNR &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, 0, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.countries
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; = 0

INSERT INTO #MY_TEMP_CNR &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242;, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.countries
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; <> 0

INSERT INTO #MY_TEMP_CNR_PARENTS
SELECT old_id, id FROM #MY_TEMP_CNR

UPDATE #MY_TEMP_CNR 
SET new_parent_id = #MY_TEMP_CNR_PARENTS.new_parent_id
FROM #MY_TEMP_CNR 
INNER JOIN #MY_TEMP_CNR_PARENTS 
ON #MY_TEMP_CNR.old_parent_id = #MY_TEMP_CNR_PARENTS.old_id

UPDATE #MY_TEMP_CNR 
SET new_parent_id = 1
WHERE old_parent_id = 1

INSERT INTO #MY_TEMP_CNR_LANG &#40;old_lang_label_id, label, intl_label&#41;
SELECT &#225;&#225;, &#208;&#229;&#241;&#233;&#227;&#241;&#225;&#246;&#222;, DescInt 
FROM old_db.dbo.countries

UPDATE #MY_TEMP_CNR 
SET label_id = #MY_TEMP_CNR_LANG.new_lang_label_id
FROM #MY_TEMP_CNR 
INNER JOIN #MY_TEMP_CNR_LANG 
ON #MY_TEMP_CNR.old_id = #MY_TEMP_CNR_LANG.old_lang_label_id

INSERT INTO #MY_TEMP_ESTATES 
SELECT &#225;&#225;, &#225;&#225;_&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#225;&#242;, date_insert, 0, '', 
	   ISNULL&#40;&#212;&#239;&#240;&#239;&#232;&#229;&#243;&#223;&#225;, ''&#41;, ISNULL&#40;NULLIF&#40;price, 0.00&#41;, PriceFrom&#41;, PriceUpTo, 
	   ISNULL&#40;NULLIF&#40;surface, 0.00&#41;, SurfaceFrom&#41;, SurfaceUpTo, 
	   ISNULL&#40;&#196;&#233;&#229;&#253;&#232;&#245;&#237;&#243;&#231;, ''&#41;, ISNULL&#40;pobox, ''&#41;, '', &#211;&#247;&#252;&#235;&#233;&#225;, 0, id_currency, id_measure, 
	   CommitionAmount, CommitionPercent, Commission2, Commission2P, 
	   EndVisible, 0, Visible, PriceNotVisible, 
	   0, Archive, checked, apokleistiko, 0 
FROM old_db.dbo.&#197;&#223;&#228;&#231;

INSERT INTO #MY_TEMP_ESTATES_DESCR&#40;old_lang_descr_id, descr, intl_descr&#41;
SELECT &#225;&#225;, &#208;&#229;&#241;&#233;&#227;&#241;&#225;&#246;&#222;, DescInt 
FROM old_db.dbo.&#197;&#223;&#228;&#231;

UPDATE #MY_TEMP_ESTATES 
SET descr_id = #MY_TEMP_ESTATES_DESCR.new_lang_descr_id 
FROM #MY_TEMP_ESTATES 
INNER JOIN #MY_TEMP_ESTATES_DESCR 
ON #MY_TEMP_ESTATES.old_id = #MY_TEMP_ESTATES_DESCR.old_lang_descr_id

INSERT INTO #MY_TEMP_ESTATES_CAT &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, 0, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; = 0

INSERT INTO #MY_TEMP_ESTATES_CAT &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242;, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; <> 0

INSERT INTO #MY_TEMP_ESTATES_CAT_PARENTS
SELECT old_id, id FROM #MY_TEMP_ESTATES_CAT

UPDATE #MY_TEMP_ESTATES_CAT 
SET new_parent_id = #MY_TEMP_ESTATES_CAT_PARENTS.new_parent_id
FROM #MY_TEMP_ESTATES_CAT 
INNER JOIN #MY_TEMP_ESTATES_CAT_PARENTS 
ON #MY_TEMP_ESTATES_CAT.old_parent_id = #MY_TEMP_ESTATES_CAT_PARENTS.old_id

UPDATE #MY_TEMP_ESTATES_CAT 
SET new_parent_id = 1
WHERE old_parent_id = 1

INSERT INTO #MY_TEMP_ESTATES_CAT_LANG &#40;old_lang_label_id, label, intl_label&#41;
SELECT &#225;&#225;, &#208;&#229;&#241;&#233;&#227;&#241;&#225;&#246;&#222;, DescInt 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;

UPDATE #MY_TEMP_ESTATES_CAT 
SET label_id = #MY_TEMP_ESTATES_CAT_LANG.new_lang_label_id
FROM #MY_TEMP_ESTATES_CAT 
INNER JOIN #MY_TEMP_ESTATES_CAT_LANG 
ON #MY_TEMP_ESTATES_CAT.old_id = #MY_TEMP_ESTATES_CAT_LANG.old_lang_label_id

UPDATE #MY_TEMP_ESTATES 
SET cat_id = #MY_TEMP_ESTATES_CAT.id 
FROM #MY_TEMP_ESTATES 
INNER JOIN #MY_TEMP_ESTATES_CAT 
ON #MY_TEMP_ESTATES.cat_id = #MY_TEMP_ESTATES_CAT.old_id

INSERT INTO #MY_TEMP_USERS_OWN &#40;new_owner_id, old_owner_id, estate_id&#41;
SELECT 0, id_card, id_item 
FROM old_db.dbo.&#91;Ownership&#93;

UPDATE #MY_TEMP_USERS_OWN 
SET estate_id = #MY_TEMP_ESTATES.id
FROM #MY_TEMP_USERS_OWN 
INNER JOIN #MY_TEMP_ESTATES 
ON #MY_TEMP_USERS_OWN.estate_id = #MY_TEMP_ESTATES.old_id

INSERT INTO #MY_TEMP_USERS &#40;old_user_id, username, &#91;password&#93;, email&#41;
SELECT aa, username, &#91;password&#93;, email 
FROM old_db.dbo.g_user_register 
WHERE active = 1

UPDATE #MY_TEMP_USERS_OWN 
SET new_owner_id = #MY_TEMP_USERS.new_user_id 
FROM #MY_TEMP_USERS_OWN 
INNER JOIN #MY_TEMP_USERS 
ON #MY_TEMP_USERS_OWN.old_owner_id = #MY_TEMP_USERS.old_user_id

INSERT INTO #MY_TEMP_USERS_CUS 
SELECT &#225;&#225;, &#225;&#225;_&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#225;&#242;, 0, aa_manage_id, 0, id_city, 
	   &#197;&#208;&#217;&#205;&#213;&#204;&#201;&#193;, &#196;&#201;&#197;&#213;&#200;&#213;&#205;&#211;&#199;, &#212;&#193;&#215;_&#202;&#217;&#196;, &#197;&#208;&#193;&#195;&#195;&#197;&#203;&#204;&#193;, '', 
	   &#212;&#199;&#203;&#197;&#214;&#217;&#205;&#207;1, &#202;&#201;&#205;&#199;&#212;&#207;, FAX, old_db.dbo.&#197;&#240;&#225;&#246;&#221;&#242;.EMAIL, URL, &#211;&#215;&#207;&#203;&#201;&#193; 
FROM old_db.dbo.&#197;&#240;&#225;&#246;&#221;&#242;
INNER JOIN old_db.dbo.&#91;Ownership&#93; 
ON old_db.dbo.&#197;&#240;&#225;&#246;&#221;&#242;.&#225;&#225; = old_db.dbo.&#91;Ownership&#93;.id_card
WHERE is_web = 0 

UPDATE #MY_TEMP_USERS_OWN 
SET new_owner_id = #MY_TEMP_USERS_CUS.new_cus_id 
FROM #MY_TEMP_USERS_OWN 
INNER JOIN #MY_TEMP_USERS_CUS
ON #MY_TEMP_USERS_OWN.old_owner_id = #MY_TEMP_USERS_CUS.old_cus_id

UPDATE #MY_TEMP_USERS_CUS 
SET new_manager_id = #MY_TEMP_USERS.new_user_id
FROM #MY_TEMP_USERS_CUS 
INNER JOIN #MY_TEMP_USERS 
ON #MY_TEMP_USERS_CUS.old_manager_id = #MY_TEMP_USERS.old_user_id

UPDATE #MY_TEMP_USERS_CUS 
SET new_country_id = #MY_TEMP_CNR.id 
FROM #MY_TEMP_USERS_CUS 
INNER JOIN #MY_TEMP_CNR
ON #MY_TEMP_USERS_CUS.old_country_id = #MY_TEMP_CNR.old_id

INSERT INTO #MY_TEMP_USERS_CUS_CAT &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, 0, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;_&#208;
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; = 0

INSERT INTO #MY_TEMP_USERS_CUS_CAT &#40;label_id, old_id, old_parent_id, new_parent_id, sort&#41;
SELECT 0, &#225;&#225;, &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242;, 0, ISNULL&#40;&#211;&#229;&#233;&#241;&#220;, 0&#41; 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;_&#208;
WHERE &#211;&#245;&#227;&#227;&#229;&#237;&#222;&#242; <> 0

INSERT INTO #MY_TEMP_USERS_CUS_CAT_PARENTS
SELECT old_id, id FROM #MY_TEMP_USERS_CUS_CAT

UPDATE #MY_TEMP_USERS_CUS_CAT 
SET new_parent_id = #MY_TEMP_USERS_CUS_CAT_PARENTS.new_parent_id
FROM #MY_TEMP_USERS_CUS_CAT 
INNER JOIN #MY_TEMP_USERS_CUS_CAT_PARENTS 
ON #MY_TEMP_USERS_CUS_CAT.old_parent_id = #MY_TEMP_USERS_CUS_CAT_PARENTS.old_id

UPDATE #MY_TEMP_USERS_CUS_CAT 
SET new_parent_id = 1
WHERE old_parent_id = 1

INSERT INTO #MY_TEMP_USERS_CUS_CAT_LANG &#40;old_lang_label_id, label, intl_label&#41;
SELECT &#225;&#225;, &#208;&#229;&#241;&#233;&#227;&#241;&#225;&#246;&#222;, DescInt 
FROM old_db.dbo.&#202;&#225;&#244;&#231;&#227;&#239;&#241;&#223;&#229;&#242;_&#208;

UPDATE #MY_TEMP_USERS_CUS_CAT 
SET label_id = #MY_TEMP_USERS_CUS_CAT_LANG.new_lang_label_id
FROM #MY_TEMP_USERS_CUS_CAT 
INNER JOIN #MY_TEMP_USERS_CUS_CAT_LANG 
ON #MY_TEMP_USERS_CUS_CAT.old_id = #MY_TEMP_USERS_CUS_CAT_LANG.old_lang_label_id

UPDATE #MY_TEMP_USERS_CUS 
SET cat_id = #MY_TEMP_USERS_CUS_CAT.id 
FROM #MY_TEMP_USERS_CUS 
INNER JOIN #MY_TEMP_USERS_CUS_CAT 
ON #MY_TEMP_USERS_CUS.cat_id = #MY_TEMP_USERS_CUS_CAT.old_id

INSERT OPENQUERY&#40;local_mysql, 'SELECT label_id, label, lang_code FROM old_db.trans_labels'&#41; 
SELECT new_lang_label_id, label, 'gr' FROM #MY_TEMP_CNR_LANG

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, label_id, parent_id, sort_order FROM old_db.countries'&#41; 
SELECT id, label_id, new_parent_id, sort FROM #MY_TEMP_CNR

INSERT OPENQUERY&#40;local_mysql, 'SELECT descr_id, description, lang_code FROM old_db.trans_real_estates_descr'&#41; 
SELECT new_lang_descr_id, descr, 'gr' FROM #MY_TEMP_ESTATES_DESCR

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, cat_id, reg_date, descr_id, serial_number, 
									  location, price, price_max, surface, surface_max, 
									  address, zip, url, notes, modification_id, currency_id, mu_id, 
									  company_commission_amount, company_commission_percent, 
									  offer_commission_amount, offer_commission_percent, 
									  view_until_date, sort_order, is_visible, is_price_visible, 
									  is_offer_commission_visible, is_archived, checked, `unique`, rating 
							   FROM old_db.real_estates'&#41; 
SELECT id, cat_id, reg_date, descr_id, serial_number, 
	   location, price, price_max, surface, surface_max, &#91;address&#93;, zip, 
	   url, notes, modification_id, currency_id, mu_id, 
	   company_commission_amount, company_commission_percent, 
	   offer_commission_amount, offer_commission_percent, 
	   view_until_date, sort_order, is_visible, is_price_visible, 
	   is_offer_commission_visible, is_archived, checked, &#91;unique&#93;, rating 
FROM #MY_TEMP_ESTATES

INSERT OPENQUERY&#40;local_mysql, 'SELECT new_id, old_id 
							   FROM old_db.temp_estates_id_mapping'&#41; 
SELECT id, old_id 
FROM #MY_TEMP_ESTATES

INSERT OPENQUERY&#40;local_mysql, 'SELECT label_id, label, lang_code FROM old_db.trans_labels'&#41; 
SELECT 120000 + new_lang_label_id, label, 'gr' FROM #MY_TEMP_ESTATES_CAT_LANG

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, label_id, parent_id, sort_order FROM old_db.real_estates_categories'&#41; 
SELECT id, 120000 + label_id, new_parent_id, sort FROM #MY_TEMP_ESTATES_CAT

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, username, password, email, phone, 
									  is_active, is_crm_user, 
									  is_crm_admin, is_affiliate 
							   FROM old_db.users'&#41; 
SELECT new_user_id, username, HashBytes&#40;'MD5', &#91;password&#93;&#41;, email, '', 
	   1, 0, 0, 0 
FROM #MY_TEMP_USERS 
WHERE username NOT IN &#40;SELECT * 
					   FROM OPENQUERY&#40;local_mysql, 'SELECT username 
													FROM old_db.users'&#41;&#41;

INSERT OPENQUERY&#40;local_mysql, 'SELECT new_id, old_id 
							   FROM old_db.temp_users_id_mapping'&#41; 
SELECT new_user_id, old_user_id 
FROM #MY_TEMP_USERS
WHERE username IN &#40;SELECT * 
				   FROM OPENQUERY&#40;local_mysql, 'SELECT username 
												FROM old_db.users'&#41;&#41;

INSERT OPENQUERY&#40;local_mysql, 'SELECT owner_id, estate_id 
							   FROM old_db.ownerships'&#41; 
SELECT new_owner_id, estate_id 
FROM #MY_TEMP_USERS_OWN

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, cat_id, crm_manager_id, country_id, 
									  company, address, zip, occupation, photo, 
									  phone, mobile, fax, email, url 
							   FROM old_db.customers'&#41; 
SELECT new_cus_id, cat_id, new_manager_id, new_country_id, 
	   company, &#91;address&#93;, zip, occupation, photo, 
	   phone, mobile, fax, email, url 
FROM #MY_TEMP_USERS_CUS 
WHERE company NOT IN &#40;SELECT * FROM OPENQUERY&#40;local_mysql, 'SELECT company 
															FROM old_db.customers'&#41;&#41;

UPDATE OPENQUERY&#40;local_mysql, 'SELECT id, notes 
							   FROM old_db.customers'&#41; 
SET notes = #MY_TEMP_USERS_CUS.notes 
FROM #MY_TEMP_USERS_CUS 
WHERE id = new_cus_id

INSERT OPENQUERY&#40;local_mysql, 'SELECT label_id, label, lang_code FROM old_db.trans_labels'&#41; 
SELECT 130000 + new_lang_label_id, label, 'gr' FROM #MY_TEMP_USERS_CUS_CAT_LANG

INSERT OPENQUERY&#40;local_mysql, 'SELECT id, label_id, parent_id, sort_order FROM old_db.customers_categories'&#41; 
SELECT id, 130000 + label_id, new_parent_id, sort FROM #MY_TEMP_USERS_CUS_CAT

TRUNCATE TABLE #MY_TEMP_CNR_PARENTS

TRUNCATE TABLE #MY_TEMP_ESTATES_CAT_PARENTS

TRUNCATE TABLE #MY_TEMP_USERS_CUS_CAT_PARENTS

DELETE FROM #MY_TEMP_CNR_LANG

DELETE FROM #MY_TEMP_CNR

DELETE FROM #MY_TEMP_ESTATES

DELETE FROM #MY_TEMP_ESTATES_DESCR

DELETE FROM #MY_TEMP_ESTATES_CAT_LANG

DELETE FROM #MY_TEMP_ESTATES_CAT

DELETE FROM #MY_TEMP_USERS

DELETE FROM #MY_TEMP_USERS_OWN

DELETE FROM #MY_TEMP_USERS_CUS

DELETE FROM #MY_TEMP_USERS_CUS_CAT_LANG

DELETE FROM #MY_TEMP_USERS_CUS_CAT

GO
/* -------------------- */



/* Drop temporary tables */
DROP TABLE #MY_TEMP_CNR

DROP TABLE #MY_TEMP_CNR_PARENTS

DROP TABLE #MY_TEMP_CNR_LANG

DROP TABLE #MY_TEMP_ESTATES

DROP TABLE #MY_TEMP_ESTATES_DESCR

DROP TABLE #MY_TEMP_ESTATES_CAT

DROP TABLE #MY_TEMP_ESTATES_CAT_PARENTS

DROP TABLE #MY_TEMP_ESTATES_CAT_LANG

DROP TABLE #MY_TEMP_USERS

DROP TABLE #MY_TEMP_USERS_OWN

DROP TABLE #MY_TEMP_USERS_CUS

DROP TABLE #MY_TEMP_USERS_CUS_CAT

DROP TABLE #MY_TEMP_USERS_CUS_CAT_PARENTS

DROP TABLE #MY_TEMP_USERS_CUS_CAT_LANG

GO

Απάντηση

Επιστροφή στο “MS SQL Server”

Μέλη σε σύνδεση

Μέλη σε αυτήν τη Δ. Συζήτηση: Δεν υπάρχουν εγγεγραμμένα μέλη και 0 επισκέπτες