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_tableHufff,
SELECT * FROM source_table
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 :
table kanan adalah table target, dan field antara table kiri dan table kanan tidak mesti sama.
- 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
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', 1Dengan menggunakan script di atas, relatif pekerjaan saya menjadi jaaaaaauh lebih simple.
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
that's it
and....
Happy coding again dude