Wednesday, February 20, 2013 8:04 PM
I have to transfer multiple table data from one database to other database with some condition.
Let see I have 50 tables in my DB_A and I want to transfer 50 tables in DB_B with condition. I want to keep only last 6 months of data records in DB_A tables, rest of the data I want to transfer in DB_B tables. So once I transfer 6 months of record from DB_A tables to DB_B tables I will delete records from DB_A tables.
Each tables in DB_A and DB_B have same structure and each table has one common column is “created Date”.
Import export is not an option if you have to put any conditions for each table.
I also don’t want to create 50 control flow / data flow in SSISpackage because those tables may be 70 in future.
- Edited by msanjay11 Thursday, February 21, 2013 1:56 AM
Wednesday, February 20, 2013 8:22 PM
I would create a procedure which can:
1. collect all tables (schema+Table Name)
2. write dynamic SQL Query, like "INSERT SELECT WHERE your condition"
and execute this dynamic query one by one
3. Use Execute SQL TASK to call this procedure
Wednesday, February 20, 2013 9:57 PM
Thank you very much Kostya,
Let me follow the step :
DECLARE @tempTable table ( id int identity not null, name varchar(100) )
insert into @tempTable (name) SELECT name FROM sys.Tables
declare @i as int = 1
while(@i<50) -- Total table
declare @tableName as varchar
set @tableName = (select name from @tempTable where id = @i)-- "Every time I will get differnt table name"
-- if data does not match than insert other wise update
insert into DB_B.table1 b select * from DB_A.table1 a
where b.id <> a.id
and a.created date is last 6 months
-- update data is id match
update DB_B.table1 b set ---
set @i = @i +1
How can I do dynamic SQL?
Thursday, February 21, 2013 5:36 AM
You have to use the cursor for the table which you have created having all the table names of source and destination as well.(See the cursor syntax online and how to use as well)
Then you have to iterate on the Dynamic query in which the table names would be stored in the variables each time but make sure you need to do select * from Source table and same data you need to insrt into the target table. if you need to choose some specific columns then you need to make the list of columns as well.
Please mark it as helpful if it helps.
Thursday, February 21, 2013 8:56 AM
If you are good in C# Programming and .net concepts, you can create your dataflow task programatically where it accepts the table name and other details dynamically by using configuration table, by this; your maintainance will be easier and in future if you want to add many more tables , you can easily add in the configuration table.
This link helps you in creating a package or dataflow task dynamically.
Else, if you are not willing to write the C# Codes, you can do as what Sumit Suggested.
Friday, February 22, 2013 5:46 PM
SELECT TABLE_SCHEMA AS tableSchema, TABLE_NAME AS tableName INTO #work_to_do FROM information_schema.tables DECLARE tablesInDB_A CURSOR FOR SELECT * FROM #work_to_do; OPEN tablesInDB_A; WHILE (1=1) BEGIN; FETCH NEXT FROM tablesInDB_A INTO @tableSchema, @tableName; IF @@FETCH_STATUS < 0 BREAK; BEGIN SET @command = N'INSERT DB_B.'+ @tableSchema +N'.'+ @tableName +' SELECT * FROM DB_A.' + @tableSchema + N'.' + @tableName + N'WHERE CreatedDate > DATEADD(month,-6,GETDATE())'; --EXEC (@command); PRINT @command END END; CLOSE tablesInDB_A; DEALLOCATE tablesInDB_A; -- Drop the temporary table. DROP TABLE #work_to_do; GO