-->
ZDIRY-TUFWT-EBONM-EYJ00-IDBLANTER.COM
ZDIRY-TUFWT-EBONM-EYJ00

BLANTERWISDOM105

Generate Script Data SQL Server Menggunakan Query

2/02/2021

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 Data SQLServer Using Query

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)
            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)
            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.

Demikianlah Cara Generate Script Data SQL Server Menggunakan Query

Share This :

0 Comments