none
Stored Procedure that copy data from tables in one database to other tables in another database

    Question

  • Hello!

    I am to move data from tables in a database to other tables in another database. The tables in the newer database have been restructured; i.e, datatypes, foreign keys have been modified. My task is to use a Stored Procedure to copy or move data from the old database to the new one as we do not use SSIS in our organisation. The challenge now is how to get started with the Procedure as I have always been used to using SSIS in my previous roles. The DBA have said this is the method they've always used in the past. 

    Please enlighten me on how this kind of Proc can be written.

    Thank you!


    • Edited by saintgr8 Wednesday, May 15, 2013 10:30 PM
    Wednesday, May 15, 2013 10:28 PM

All replies

  • Hi,

    It seems you have a huge task ahead.

    If you need to move the data only once, you don't need a stored procedure, you can do it by using T-SQL statements like INSERT from SELECT. Well, a SP is a set of T-SQL statements.

    At first, I recommend you to use a transaction (all data is successfully transferred or not).

    You also need to understand the older and the newer database structure, in order to know the match between the source and destination tables / columns.

    If you're data types suffered (minor) changes, you can you use CAST and CONVERT functions in order to fill the data correctly.

    I also recommend you to write the procedure (and test it) using a test database and, when migration looks good, then run it on the production database.

    Hope this helps,

    Good luck with your migration!


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa


    • Edited by Rechousa Wednesday, May 15, 2013 10:48 PM
    Wednesday, May 15, 2013 10:48 PM
  • Hi,

    Follow this to create the SP for data transfer

    http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx


    Many Thanks & Best Regards, Hua Min

    Thursday, May 16, 2013 1:45 AM
  • Hello!

    I am to move data from tables in a database to other tables in another database. The tables in the newer database have been restructured; i.e, datatypes, foreign keys have been modified. My task is to use a Stored Procedure to copy or move data from the old database to the new one as we do not use SSIS in our organisation. The challenge now is how to get started with the Procedure as I have always been used to using SSIS in my previous roles. The DBA have said this is the method they've always used in the past. 

    Please enlighten me on how this kind of Proc can be written.

    Thank you!


    Try Like  this

    USE tempdb
    GO
     CREATE TABLE SAMPLE2
    ( LDID VARCHAR(30) PRIMARY KEY,
      FIELD1 VARCHAR(30),
      FIELD2 VARCHAR(30),
      FIELD3 VARCHAR(30))

      INSERT INTO SAMPLE2 VALUES(1,'JAN:3','FEB:3','MARCH:4');
       INSERT INTO SAMPLE2 VALUES(2,'JAN:4','FEB:4','MARCH:5');
       INSERT INTO SAMPLE2 VALUES(3,'JAN:5','FEB:6','MARCH:7');

       GO
    USE master
    GO
     CREATE TABLE SAMPLE2
    ( LDID VARCHAR(30) PRIMARY KEY,
      FIELD1 VARCHAR(30),
      FIELD2 VARCHAR(30),
      FIELD3 VARCHAR(30))

      -- QUERY TO INSERT THE DATA
    INSERT INTO master..SAMPLE2
    SELECT * FROM TEMPDB..SAMPLE2

    -- WITH PROCEDURE
    CREATE PROCEDURE TEST_SP
    AS
    BEGIN
    SELECT 1
    --    INSERT INTO master..SAMPLE2
    --SELECT * FROM TEMPDB..SAMPLE2
    END

    -- EXECUTE
    EXEC TEST_SP

    But before that u should take care on constrains like primary key and Data types.

    Thanks


    bala krishna

    Thursday, May 16, 2013 4:27 AM
  • Its a really big task in absence of SSIS. But you have to create SPs based on the table constraints and etc...

    Plan for the table data to be moved. 

    Use Identity Insert on or Off

    Disabling constraints,Triggers.

    But it will take plenty of time based on your requirement.

    Thursday, May 16, 2013 6:09 AM