How to transfer multiple tables data from one database to other database with some condition.

All Replies

• Wednesday, February 20, 2013 8:22 PM

   

   
  

  1

  Hi,

  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

  • Reply
  • Quote

   
• Wednesday, February 20, 2013 9:57 PM

   

   
  

  0

  Thank you very much Kostya,

  Let me follow the step :

  (Step 1)

  DECLARE @tempTable table ( id int identity not null, name varchar(100) )

  insert into @tempTable (name) SELECT name FROM sys.Tables

  (setp 2)

  declare @i as int = 1

  while(@i<50) -- Total table

  begin

        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

  end

  How can I do dynamic SQL?

  Thanks,
  
  

  • Edited by msanjay11 Wednesday, February 20, 2013 10:00 PM
  • Edited by msanjay11 Thursday, February 21, 2013 1:54 AM
  •  
  • Reply
  • Quote

   
• Thursday, February 21, 2013 5:36 AM

   

   
  

  1

  Hi Sanjay,

  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.

  Thanks

  Sumit

  • Proposed As Answer by Mike YinMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 9:25 AM
  • Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Tuesday, February 26, 2013 9:37 AM
  •  
  • Reply
  • Quote

   
• Thursday, February 21, 2013 8:56 AM

   

   
  

  1

  Hi Sanjay,

  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.

  http://msdn.microsoft.com/en-us/library/ms135946.aspx

  Else, if you are not willing to write the C# Codes, you can do as what Sumit Suggested.

  Regards

  Naveen

  • Proposed As Answer by Mike YinMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 9:25 AM
  • Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Tuesday, February 26, 2013 9:37 AM
  •  
  • Reply
  • Quote

   
• Friday, February 22, 2013 5:46 PM

   

   
  

  1

  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

  
  

  • Proposed As Answer by Kostya Khomyakov Friday, February 22, 2013 5:46 PM
  • Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Tuesday, February 26, 2013 9:37 AM
  •  
  • Reply
  • Quote