x

How to import multiple SQL Server tables of same schema into SQL Server using SSIS/ a Stored procedure

Hello All,

I have a data request to import all SQL Server table of same schema into one new table (Master_table) regularly.

I have tables created daily like,

table_20150701 table_20150702 table_20150703 table_20150705 . . . table_20150716 like this everyday I will get data feeds from external sources to a table in on of my database, the table has a lastUpdate column as well with the datestamp.

My request is to import all the tables data into one single table (Master_table) and add the next days data directly into the new file

Next day when it creates a table_2015xxxx... it should automatically load data int to new table (Master_table)

This is a continuous process so we can use the new table for analysis purpose.

Appreciate your quick help!!

Thanks

more ▼

asked Jul 16, 2015 at 05:25 PM in Default

avatar image

repalaa
41 1 1 5

If not in SSIS, please suggest me either if this can by writing using a store procedure

Jul 16, 2015 at 05:27 PM repalaa

I have tables from 2012, like I have close to 800 tables now in my database with name as tablename_yyyymmdd.

Now I want to make all in one new table.

And from tomorrow when a table is created with tablename_yyyymmdd, a procedure or ssia package should write all the date into new table we have created.

and the next step is to eliminate the temp/stage table and load data directly into the master table or., new table.

Thanks again.

Jul 16, 2015 at 11:15 PM repalaa
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Here's a quick sample to get you going.

1) Create Master_table with columns to match daily tables plus a date column to show what date the row was originally imported and a nice little identity column for the clustered index

2) Create insert statements to load the daily tables into Master_table then drop the daily tables.

3) Run the generated statements

 use tempdb;
 
 -- one historical table per day
 create table dbo.table_20150101 (col1 int, col2 nvarchar(255));
 create table dbo.table_20150102 (col1 int, col2 nvarchar(255));
 create table dbo.table_20150103 (col1 int, col2 nvarchar(255));
 
 insert dbo.table_20150101 select top 100 object_id, name from sys.all_objects order by newid();
 insert dbo.table_20150102 select top 100 object_id, name from sys.all_objects order by newid();
 insert dbo.table_20150103 select top 100 object_id, name from sys.all_objects order by newid();
 
 -- 1. single historical table to replace daily tables
 create table dbo.Master_table (Master_table_Id bigint identity(1,1) constraint pk__dbo__Master_table__pk primary key clustered, row_date date, col1 int, col2 nvarchar(255));
 
 -- 2.  build queries to load daily tables into Master table then drop the daily tables
 declare @cleanup_script nvarchar(max) = N'';
 
 select 
 @cleanup_script +=
 'BEGIN TRAN
 BEGIN TRY
     insert dbo.Master_table select convert(date, ''' 
 + substring(name, 7, 4) + '-' + substring(name, 11, 2) + '-' + substring(name, 13, 2)
 + '''), col1, col2 
     from [' + schema_name(schema_id) + '].[' + name +']
 
     DROP TABLE ' + name + '
 
     COMMIT TRAN
 END TRY
 BEGIN CATCH
     SELECT ''' + schema_name(schema_id) + '.' + name + ''' AS problem_table, error_number() as [error_number], error_message() as [error_message]
     ROLLBACK TRAN
 END CATCH
 
 '
 from sys.tables 
 where name like 'table[_]201[0-9][0-1][0-9][0-3][0-9]'
 order by name;
 
 print @cleanup_script;

 -- 3.  import the rows and drop the daily tables
 exec sp_executesql @cleanup_script;
 
 select * from dbo.Master_table;
 
 drop table dbo.Master_table;
more ▼

answered Jul 18, 2015 at 06:03 AM

avatar image

KenJ
25k 3 10 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x1219
x438
x58

asked: Jul 16, 2015 at 05:25 PM

Seen: 160 times

Last Updated: Jul 18, 2015 at 06:03 AM

Copyright 2017 Redgate Software. Privacy Policy