Dynamic Query for Insert SQL Server 2005 (Without Loop)

Mau sharing, kebetulan dari sekian system yang saya support, ada satu system yang punya kekurangan masalah feeding data. Jadinya klo lagi problem, saya harus feeding data secara manual table to table. Awalnya insert satu-satu, coba iseng bikin dynamic query, tapi g pake looping, klo pake looping sebenarnya udah bisa.

Berikut script nya:

SELECT  MAIN.OBJECT_ID ,
        'INSERT INTO ' + OBJECT_NAME(MAIN.OBJECT_ID) + ' ('
        + LEFT(MAIN.STUDENTS, LEN(MAIN.STUDENTS) - 1) + ') SELECT '
        + LEFT(MAIN.STUDENTS, LEN(MAIN.STUDENTS) - 1) + ' FROM '
        + OBJECT_NAME(MAIN.OBJECT_ID) AS "STUDENTS"
FROM    ( SELECT DISTINCT
                    ST2.OBJECT_ID ,
                    ( SELECT    ST1.NAME + ',' AS [TEXT()]
                      FROM      SYS.COLUMNS ST1
                      WHERE     ST1.OBJECT_ID = ST2.OBJECT_ID
                      ORDER BY  ST1.OBJECT_ID
                    FOR
                      XML PATH('')
                    ) [STUDENTS]
          FROM      SYS.COLUMNS ST2
        ) [MAIN]

Semoga bermanfaat.

Fyi, saya dapat scriptnya disini.

Marifnst, 20160905

Leave a Reply Cancel reply