Data Migration, it's a T-SQL way

Sebagai developer freelance, seringkali saya harus memindahkan/menyalin sebagian data table dari satu database ke database lain, istilah kerennya partial migration (mbuh bener, mbuh gak istilah'e, ngawur ae).

Hal ini seringkali memusingkan saya, apalagi karena kebanyakan saya menggunakan increment field sebagai primary key, dimana satu field pada table saya set sebagai identity field dengan increment 1.

Akhirnya.....

Ketika tiba bagi saya untuk melakukan partiap migration, dimana hanya table-table tertentu yang ingin saya update datanya (insert tentunya), selalu terbentur dengan masalah indetity field ini. Disamping itu, jika kebetulan ada table dengan field yang tidak sama, maka tentu saya tidak lagi dapat menggunakan INSERT andalan :

INSERT INTO target_table
SELECT * FROM source_table

Hufff,
Betapa susahnya jika saya harus mendefinisikan pada SQL statement saya satu persatu field-field yang harus saya insertkan.

Setelah benar-benar bosan dengan cara "usang" ini, akhirnya saya coba untuk eksplore apakah mungkin untuk melakukan migrasi sekian table dalam 1 script.

Well, akhirnya dengan memanfaatkan table sysobjects dan syscolumns saya buat satu scipt untuk melakukan partial migration beberapa table sekaligus.

Hal pertama yang saya lakukan adalah mencopy table-table yang akan dimigrasi ke database local, sebenarnya dapat juga langsung ke database sourcenya, namun untuk kemudahan, saya copy saja ke database local saya.

Let say saya punya 10 table yang akan saya migrasikan :

• tmk_fml --> t_fml
• tmk_hst_ctr --> t_hst_ctr
• tmk_hst_edu --> t_hst_edu
• tmk_mem --> t_mem
• tmk_rem_emp --> t_rem_emp
• tmk_mst_emp --> t_mst_emp
• tmk_sys_hist --> tsys_hist
• tmp_fnc --> t_fnc
• tmp_org --> t_org
• tmp_prc_crt --> t_prc_crt

table kanan adalah table target, dan field antara table kiri dan table kanan tidak mesti sama.

Idenya adalah, bagaimana mendapatkan field-field yang sama antara table kiri dan table kanan, kemudian dengan menggunakan sp_executesql akan menjalankan satu SQL String. Disamping itu, karena pada table kanan ada identity field, maka identity field harus diset inactive, sehingga nantinya identity field tersebut akan mengikuti identity field pada table kiri.

Let's see the code here :

EXEC sp_configure 'allow update', 1
RECONFIGURE WITH OVERRIDE
GO

DECLARE @ref TABLE (rid INT, ta VARCHAR(50), ti VARCHAR(50))
DECLARE @tname VARCHAR(50), @id NUMERIC, @sql NVARCHAR(4000), @a INT, @b INT
DECLARE @x INT, @y INT, @i INT, @n INT, @columns VARCHAR(3000), @ta VARCHAR(50), @ti VARCHAR(50)
DECLARE @table TABLE (id INT IDENTITY(1, 1), name VARCHAR(200))

SELECT @a = 0, @b = 0

INSERT INTO @ref (rid, ta, ti)
SELECT 1, 'tsys_hist', 'tmk_sys_hist'
UNION
SELECT 2, 't_fnc', 'tmp_fnc'
UNION
SELECT 3, 't_org', 'tmp_org'
UNION
SELECT 4, 't_prc_crt', 'tmp_prc_crt'
UNION
SELECT 5, 't_mst_emp', 'tmk_mst_emp'
UNION
SELECT 6, 't_fml', 'tmk_fml'
UNION
SELECT 7, 't_hst_ctr', 'tmk_hst_ctr'
UNION
SELECT 8, 't_hst_edu', 'tmk_hst_edu'
UNION
SELECT 9, 't_mem', 'tmk_mem'
UNION
SELECT 10, 't_rem_emp', 'tmk_rem_emp'



SELECT * FROM @ref

SELECT @y = COUNT(*), @x = 1 FROM @ref

WHILE @x<=@y
BEGIN

SELECT @ta = ta, @ti = ti FROM @ref WHERE rid = @x

SELECT @id = id FROM sysobjects WHERE name = @ta

DELETE FROM @table

INSERT INTO @table (name)
SELECT a.name FROM
(SELECT * FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @ta)) AS a
INNER JOIN
(SELECT * FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @ti)) AS b ON a.name = b.name

SELECT @i = @a+1, @n = @b+COUNT(*), @columns = '' FROM @table
WHILE @i<=@n
BEGIN
SELECT @columns = @columns + name + ', ' FROM @table WHERE id = @i
SET @i = @i + 1
END

SET @columns = SUBSTRING(@columns, 1, LEN(@columns)-1)

UPDATE syscolumns SET colstat = 0 WHERE id = @id
SET @sql = 'INSERT INTO '+ @ta + ' (' + @columns + ') SELECT ' + @columns +' FROM ' + @ti
PRINT @sql
EXEC sp_executesql @sql
UPDATE syscolumns SET colstat = 1 WHERE id = @id

SELECT @a= @i, @b = @n
SET @x = @x + 1
END

EXEC sp_configure 'allow update', 0
RECONFIGURE WITH OVERRIDE
GO

Dengan menggunakan script di atas, relatif pekerjaan saya menjadi jaaaaaauh lebih simple.

that's it

and....

Happy coding again dude