Problem
Proses analisis atau testing suatu batch sql server,
akan selalu melakukan proses insert delete, dan update. Dalam proses insert
mungkin kita dapat menggunakan data dummy atau data yang sudah di backup ke
dalam file excel ataupun csv. Misalkan kita akan melakukan pemindahan data dari
Database pada server 1 ke Database server 2. Untuk melakukannya dapat
menggunakan fitur Import ataupun export pada Sql Server Management Studio.
Masalahnya jika Database server 2 fitur untuk import datanya di nonaktifkan apa
yang dapat dilakukan?Biasanya dengan melakukan Generate Script.
Generate Script ini belum dapat menggunakan query yang
mana tidak dapat menggunakan trigger apapun (where criteria). Jika data yang dibutuhkan hanya beberapa saja pastinya tidak bisa dilakukan.
Solusi
Generate script adalah suatu mekanisme untuk menghasil
script(text) Transact-SQL, yang mana script tersebut dapat berupa coding create
table, stored procedures atau pun insert data dan lain-lain. Dikarenakan fitur generate script pada Server Management Studio tidak dapat menggunakan query
maka Agung Panduan akan membagikan cara generate script menggunakan query yaitu,
Pertama, Coba buat suatu table seperti di bawah ini,
CREATE TABLE [dbo].[TB_M_USER](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[USERID] [varchar](30) NOT NULL,
[PASSWORD] [varchar](50) NOT NULL,
[USERNAME] [varchar](10) NOT NULL,
[VALID] Date NOT NULL,
[CREATED_DT] Datetime NOT NULL,
) ON [PRIMARY]
insert into TB_M_USER values(1,'rahasi@','opensay','2020-12-31',getdate()) -- ^_^
insert into TB_M_USER values(2,'nikm4t','puasya','2020-11-30',getdate()) -- ^_^
insert into TB_M_USER values(3,'nyes3l','kurang','2020-11-30',getdate()) -- ^_^
insert into TB_M_USER values(4,'b0k3','bayar','2020-12-31',getdate()) -- ^_^
insert into TB_M_USER values(5,'cop0t','kutang','2020-12-31',getdate()) -- ^_^
Kedua, ganti Table Name dan Criteria menggunakan TB_M_USER dan where WHERE VALID=’’2020-11-30’’ pada script di bawah ini,
-- Make sure you're on the correct database
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Tables TABLE (
TableName varchar(50) NOT NULL,
Arguments varchar(1000) NULL
);
-- INSERT HERE THE TABLES AND CONDITIONS YOU WANT TO GENERATE THE INSERT STATEMENTS
INSERT INTO @Tables (TableName, Arguments) VALUES ('TABLE NAME', 'CRITERIA');
-- (ADD MORE LINES IF YOU LIKE) INSERT INTO @Tables (TableName, Arguments) VALUES ('table2', 'WHERE field2 IN (1, 3, 5)');
-- YOU DON'T NEED TO EDIT FROM NOW ON.
-- Generating the Script
-- AGUNGPANDUAN.COM
DECLARE @TableName varchar(50),
@Arguments varchar(1000),
@ColumnName varchar(50),
@strSQL varchar(max),
@strSQL2 varchar(max),
@Lap int,
@Iden int,
@TypeOfData int;
DECLARE C1 CURSOR FOR
SELECT TableName, Arguments FROM @Tables
OPEN C1
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
WHILE @@FETCH_STATUS = 0
BEGIN
-- If you want to delete the lines before inserting, uncomment the next line
-- PRINT 'DELETE FROM ' + @TableName + ' ' + @Arguments
SET @strSQL = 'INSERT INTO ' + @TableName + ' (';
-- List all the columns from the table (to the INSERT into columns...)
SET @Lap = 0;
DECLARE C2 CURSOR FOR
SELECT sc.name, sc.type FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id AND so.name = @TableName AND so.type = 'U' WHERE sc.colstat = 0 ORDER BY sc.colorder
OPEN C2
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0) --@lap lebih besar(more than)
BEGIN
SET @strSQL = @strSQL + ', ';
END
SET @strSQL = @strSQL + @ColumnName;
SET @Lap = @Lap + 1;
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
END
CLOSE C2
DEALLOCATE C2
SET @strSQL = @strSQL + ')'
SET @strSQL2 = 'SELECT ''' + @strSQL + '
SELECT '' + ';
-- AGUNGPANDUAN.COM
-- List all the columns from the table again (for the SELECT that will be the input to the INSERT INTO statement)
SET @Lap = 0;
DECLARE C2 CURSOR FOR
SELECT sc.name, sc.type FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id AND so.name = @TableName AND so.type = 'U' WHERE sc.colstat = 0 ORDER BY sc.colorder
OPEN C2
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0) --@lap lebih besar(more than)
BEGIN
SET @strSQL2 = @strSQL2 + ' + '', '' + ';
END
-- For each data type, convert the data properly
IF(@TypeOfData IN (55, 106, 56, 108, 63, 38, 109, 50, 48, 52)) -- Numbers
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (62)) -- Float Numbers
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), CONVERT(decimal(18,5), ' + @ColumnName + ')), ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (61, 111)) -- Datetime
SET @strSQL2 = @strSQL2 + 'ISNULL( '''''''' + CONVERT(varchar(max),' + @ColumnName + ', 121) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (0)) -- Datetime
SET @strSQL2 = @strSQL2 + 'ISNULL( '''''''' + CONVERT(varchar(max),' + @ColumnName + ', 121) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (47, 39)) -- Texts
SET @strSQL2 = @strSQL2 + 'ISNULL('''''''' + LTRIM(RTRIM(' + @ColumnName + ')) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
ELSE -- Unknown data types
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '(INCORRECT TYPE ' + CONVERT(varchar(10), @TypeOfData) + ')''';
SET @Lap = @Lap + 1;
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
END
CLOSE C2
DEALLOCATE C2
SET @strSQL2 = @strSQL2 + ' as [-- ' + @TableName + ']
FROM ' + @TableName + ' WITH (NOLOCK) ' + @Arguments
SET @strSQL2 = @strSQL2 + ';
';
--PRINT @strSQL;
PRINT @strSQL2;
EXEC(@strSQL2);
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
END
CLOSE C1
DEALLOCATE C1
-- AGUNGPANDUAN.COM
ROLLBACK
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 0 AS Situacao;
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage,
@strSQL As strSQL,
@strSQL2 as strSQL2;
END CATCH
Maka, akan menghasilkan Transact-SQL (Insert)
INSERT INTO TB_M_USER (USERID, PASSWORD, USERNAME, VALID, CREATED_DT)
SELECT '2' as USERID, 'nikm4t' as PASSWORD, 'puasya' as USERNAME, '2020-11-30' as VALID, '2021-02-03 14:40:32.130' as CREATED_DT
INSERT INTO TB_M_USER (USERID, PASSWORD, USERNAME, VALID, CREATED_DT)
SELECT '3' as USERID, 'nyes3l' as PASSWORD, 'kurang' as USERNAME, '2020-11-30' as VALID, '2021-02-03 14:40:32.130' as CREATED_DT
Sekarang lakukan delete data yang validnya hanya
tanggal 2020-11-30
Data execute Transact-SQL (Insert) yang dihasilkan.
Generate Script Using Query Table Temporary
--Create Table Temporary
CREATE TABLE #TABLE_TEST (
id int not null,
name varchar(100) not null
)
-- Get Data from Datasource
Insert into #TABLE_TEST
Select * from table where ....
-- Make sure you're on the correct database
-- Make sure you're on the correct database
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Tables TABLE (
TableName varchar(50) NOT NULL,
Arguments varchar(1000) NULL
);
-- INSERT HERE THE TABLES AND CONDITIONS YOU WANT TO GENERATE THE INSERT STATEMENTS
INSERT INTO @Tables (TableName, Arguments) VALUES ('#TABLE_TEST', '
');
-- (ADD MORE LINES IF YOU LIKE) INSERT INTO @Tables (TableName, Arguments) VALUES ('table2', 'WHERE field2 IN (1, 3, 5)');
-- YOU DON'T NEED TO EDIT FROM NOW ON.
-- Generating the Script
-- www.agungcode.com
DECLARE @TableName varchar(50),
@Arguments varchar(1000),
@ColumnName varchar(max),
@strSQL varchar(max),
@strSQL2 varchar(max),
@Lap int,
@Iden int,
@TypeOfData varchar(max);
DECLARE C1 CURSOR FOR
SELECT TableName, Arguments FROM @Tables
OPEN C1
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
WHILE @@FETCH_STATUS = 0
BEGIN
-- If you want to delete the lines before inserting, uncomment the next line
-- PRINT 'DELETE FROM ' + @TableName + ' ' + @Arguments
SET @strSQL = 'INSERT INTO ' + @TableName + ' (';
-- List all the columns from the table (to the INSERT into columns...)
-- www.agungcode.com
SET @Lap = 0;
DECLARE C3 CURSOR FOR
SELECT sc.name, ty.name FROM tempdb.sys.columns sc
INNER JOIN tempdb.sys.objects so ON so.object_id = sc.object_id and so.name like @TableName +'%' AND so.type = 'U'
JOIN sys.types ty ON sc.user_type_id = ty.user_type_id
order by sc.column_id
OPEN C3
FETCH NEXT FROM C3 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0) --@lap lebih besar(more than)
BEGIN
SET @strSQL = @strSQL + ', ';
END
SET @strSQL = @strSQL + @ColumnName;
SET @Lap = @Lap + 1;
FETCH NEXT FROM C3 INTO @ColumnName, @TypeOfData;
END
CLOSE C3
DEALLOCATE C3
SET @strSQL = @strSQL + ')'
SET @strSQL2 = 'SELECT ''' + @strSQL + '
SELECT '' + ';
-- List all the columns from the table again (for the SELECT that will be the input to the INSERT INTO statement)
-- www.agungcode.com
SET @Lap = 0;
DECLARE C3 CURSOR FOR
SELECT sc.name, ty.name FROM tempdb.sys.columns sc
INNER JOIN tempdb.sys.objects so ON so.object_id = sc.object_id and so.name like @TableName +'%' AND so.type = 'U'
JOIN sys.types ty ON sc.user_type_id = ty.user_type_id
order by sc.column_id
OPEN C3
FETCH NEXT FROM C3 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0) --@lap lebih besar(more than)
BEGIN
SET @strSQL2 = @strSQL2 + ' + '', '' + ';
END
-- For each data type, convert the data properly
IF(@TypeOfData ='numeric')
BEGIN
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '''';
END
ELSE IF(RTRIM(LTRIM(@TypeOfData)) ='datetime')
BEGIN
print @ColumnName
SET @strSQL2 = @strSQL2 + 'ISNULL( '''''''' + CONVERT(varchar(max),' + @ColumnName + ', 121) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
END
ELSE --IF(@TypeOfData ='varchar')
BEGIN
SET @strSQL2 = @strSQL2 + 'ISNULL('''''''' + RTRIM(LTRIM(' + @ColumnName + ')) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
END
--ELSE
--BEGIN
-- SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '(INCORRECT TYPE ' + CONVERT(varchar(10), @TypeOfData) + ')''';
--END
SET @Lap = @Lap + 1;
FETCH NEXT FROM C3 INTO @ColumnName, @TypeOfData;
END
CLOSE C3
DEALLOCATE C3
SET @strSQL2 = @strSQL2 + ' as [-- ' + @TableName + ']
FROM ' + @TableName + ' WITH (NOLOCK) ' + @Arguments
SET @strSQL2 = @strSQL2 + ';
';
--PRINT @strSQL;
--PRINT @strSQL2;
print 'www.agungcode.com'
-- www.agungcode.com
EXEC(@strSQL2);
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
END
CLOSE C1
DEALLOCATE C1
ROLLBACK
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 0 AS Situacao;
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage,
@strSQL As strSQL,
@strSQL2 as strSQL2;
END CATCH
DROP TABLE #TABLE_TEST
Share This :
replace > to >
BalasHapusreplace @lap'>' to @lap>
BalasHapus