You must Sign In to post a response.
  • Category: SQL Server

    Insert data from a table from one database to another database

    Hi,

    I have to write a stored proc, which will read teh data from one database table and insert into another database table if record is not in the destination table. both aource and destination table have same structure.

    in my source db and destination db i have table structure like this

    master table - pk - id
    child table1 - fk - master id, pk- id
    child table2 - fk - master id, fk- t1 -id,
    child table3 - fk - master id, fk- t1 -id,
    child table4 - fk - master id, fk- t1 -id

    now in my destination DB i have to check if id from source master table exist
    thn i have to check for this id in destination db i have records in child table1 for same date as for source child table1, if not then i have to insert data in child tables.

    if id from master table dose not exist then i have to insert data into dstination Db for all teh tables.

    Please let me know if i am not able to explain the scenario.

    Thanks in advance.
    Shubha
  • #582627
    Hi,
    As per this scenario you need to take help of some programming language features like if statement and then based on their result you can insert
    data into appropriate tables.

    Regards
    Naresh

  • #582689
    Hi,

    You can see this thread.
    http://shatrugna.blogspot.com/2010/04/get-record-from-another-db-table.html

    Thanks!!!
    Shatrughna

  • #582752
    try this query and implement.
    i think you got idea for insertion data database to another database



    SELECT *
    INTO DestinationDB..MyDestinationTable
    FROM SourceDB..MySourceTable



    hope this will help you

  • #582939
    Hi
    Try this ..




    CREATE PROCEDURE [dbo].InsertData]
    @id as int,
    AS
    BEGIN

    if not exists (select * from SourceDataBaseName.dbo.MasterTableName where id=@id)

    begin
    SELECT *
    INTO Destination..MasterTableName
    FROM SourceDataBaseName.dbo.MasterTableName

    end


    GO



    Thanks & Regards
    Lion
    Jaipur (Pink City) India

  • #583908
    Thanks all for the reply.
    i have done somthing like lion suggested.. but i had to loop through in all the tables.. so using cursors. i am lucky that data will not be much so cursor will not slowdown the performance.

    I am ready with another question regarding entity data model and its weird behaviour.


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.