declare kursor cursor for 
    select 
        exec_No, insert_type, stg_table, src 
    from 
        METADATA.dbo.METADATA 
    order by 
        exec_No asc;

    declare @insert_type varchar(15), @stg_table varchar(30), 
            @src varchar(80), @SQL varchar(254), @exec_No numeric(2), 
            @SQL_INSERT varchar(254);

    open kursor

    fetch next from kursor into @exec_No, @insert_type, @stg_table, @src;

    while @@FETCH_STATUS = 0
    begin
        if @insert_type = 'select' 
        begin
            --  select @SQL = 'IF OBJECT_ID('''[email protected]_table+''') IS NOT NULL DROP TABLE '[email protected]_table;
            select @SQL = 'DROP TABLE IF EXISTS '[email protected]_table;
            select @SQL_INSERT = 'select * from '''[email protected]+''' as '[email protected]_table;
            exec(@SQL);
            execute(@SQL_INSERT);

            fetch next from kursor into @exec_No, @insert_type, @stg_table, @src;
        end 

        if @insert_type = 'bulk'
        begin
            execute ('truncate table ' + @stg_table);
            execute('BULK INSERT '[email protected]_table+' 
                     FROM ''' + @src + ''' 
                     WITH
                         (
                            FIELDTERMINATOR = '',''
                         )')
        end

        fetch next from kursor into @exec_No, @insert_type, @stg_table, @src;
    end

    close kursor;
    deallocate kursor;

and I get errors

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SOURCE.dbo.SRC_KLIENT'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SOURCE.dbo.SRC_MIEJSCOWOSC'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SOURCE.dbo.SRC_PRACOWNIK'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SOURCE.dbo.SRC_MELDUNEK'.

Errors are generated by statement :

select @SQL_INSERT = 'select * from '''[email protected]+''' as '[email protected]_table;

my inserts for metadata :

insert into 
METADATA.dbo.METADATA(exec_No,src,stg_table,temp_table, chd_table, insert_type) 
values(1,'SOURCE.dbo.SRC_KLIENT', 'STAGE.dbo.STG_KLIENT', 'TEMP.dbo.TEMP_KLIENT', 'CHD.dbo.KLIENT', 'select');

insert into 
METADATA.dbo.METADATA(exec_No,src,stg_table,temp_table, chd_table, insert_type) 
values(15,'SOURCE.dbo.SRC_MELDUNEK', 'STAGE.dbo.STG_MELDUNEK', 'TEMP.dbo.TEMP_MELDUNEK', 'CHD.dbo.MELDUNEK', 'select');

Any ideas how to get it working?

Thanks guys !

1 Answers

0
marekswiderek On

I've finally done this. There's a working(I do right now know it's not safe) code:

declare 
    kursor cursor for select exec_No, insert_type, stg_table, src from METADATA.dbo.METADATA order by exec_No ASC;
    declare @insert_type varchar(15), @stg_table varchar(30), @src varchar(80), @SQL varchar(254), @exec_No numeric(2), @SQL_INSERT varchar(254);
    open kursor
    fetch next from kursor
    into @exec_No, @insert_type, @stg_table, @src;

    WHILE @@FETCH_STATUS = 0
        begin
            if @insert_type = 'select' 
            begin
                select @SQL = 'DROP TABLE IF EXISTS '[email protected]_table;
                select @SQL_INSERT = 'select * into ' + @stg_table + ' from ' + @src;
                print(@SQL);
                print(@SQL_INSERT);
                exec(@SQL);
                exec(@SQL_INSERT);
            end 
            if @insert_type = 'bulks'
            begin
            execute ('truncate table ' + @stg_table);
            execute('BULK INSERT '[email protected]_table+' 
                        FROM ''' + @src + ''' 
                        WITH
                        (
                        FIELDTERMINATOR = '',''
                        )')
            end
            fetch next from kursor
            into @exec_No, @insert_type, @stg_table, @src;
        end

    CLOSE kursor;
    deallocate kursor;

In SQL Server select * from table_1 as table_2 isn't correct query. Correct one is select * into table_2 from table_1.

Also there were duplicated 'fetch next ...' blocks(one inside first if, another one outside ifs).

Thanks for help guys !