-- cleaning up -- DROP TABLE dbo.LoadTables -- defining the scope SELECT * INTO dbo.LoadTables FROM (VALUES ('dbo.InventTable') , ('dbo.InventDimCombination') , ('dbo.InventDim') , ('dbo.InventItemLocation')) DAT ([Table])
-- creating the stored procedure CREATE PROCEDURE dbo.pLoadData( @Table as nvarchar(50)) AS /* loads the set of tables defiend in dbo.LoadTables */ BEGIN DECLARE @cTable varchar(50) -- creating the cursor DECLARE TableList CURSOR FOR SELECT [Table] FROM dbo.LoadTables WHERE [Table] = IsNull(@Table, [Table]) ORDER BY [Table] -- opening the cursor OPEN TableList -- fetching next record FETCH NEXT FROM TableList INTO @cTable -- looping through each record WHILE @@FETCH_STATUS = 0 BEGIN --- preparing the DROP TABLE statement SELECT(' DROP TABLE IF EXISTS ' + @cTable + '') -- preparing the SELECT INTO STATEMENT SELECT( ' SELECT *' + ' INTO ' + @cTable + ' FROM [server].[database].[' + @cTable + ']') -- fetching next record FETCH NEXT FROM TableList INTO @cTable END --closing the cursor CLOSE TableList -- deallocating the cursor DEALLOCATE TableList END
Running the stored procedure for all the tables:
-- Testing the procedure EXEC dbo.pLoadData NULL -- loading all tables -- output DROP TABLE IF EXISTS dbo.InventDim SELECT * INTO dbo.InventDim FROM [server].[database].[dbo.InventDim] DROP TABLE IF EXISTS dbo.InventDimCombination SELECT * INTO dbo.InventDimCombination FROM [server].[database].[dbo.InventDimCombination] DROP TABLE IF EXISTS dbo.InventItemLocation SELECT * INTO dbo.InventItemLocation FROM [server].[database].[dbo.InventItemLocation] DROP TABLE IF EXISTS dbo.InventTable SELECT * INTO dbo.InventTable FROM [server].[database].[dbo.InventTable]
Running the stored procedure for a specific table:
-- Testing the procedure
EXEC dbo.pLoadData 'dbo.InventTable' -- loading a specific table -- output DROP TABLE IF EXISTS dbo.InventTable SELECT * INTO dbo.InventTable FROM [server].[database].[dbo.InventTable]
Notes:
Having an old example of using a cursor (see Cursor and Lists) the whole mechanism for loading the data was available in 30 Minutes or so.
Tables can be added or removed after need, and the loading can be made more flexible by adding other parameters to the logic.
The solution is really easy to use and the performance is as well acceptable in comparison to SSIS packages.
Probably you already observed the use of DROP TABLE IF EXSISTS introduced with SQL Server 2016 (see also post)
Advantages:The stored procedure can be extended to any database for which can be created a linked server.
Structural changes of the source tables are reflected in each load.
Tables can be quickly updated when needed just by executing the stored procedure.
Disadvantages:
Such solutions are more for personal use and their use should be avoided in a production environment.
The metadata will be temporarily unavailable during the time the procedure is run. Indexes need to be created after each load.
Happy Coding!
