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

    Insert data from a table from one database to another database


    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.
  • #582627
    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.


  • #582689

    You can see this thread.


  • #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
    Try this ..

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

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

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



    Thanks & Regards
    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.