CODEWITHSUNDEEP
sql-server
Ramachandran
Ramachandran

Reputation: 11

How do I copy the data from one table to another identical table using SQL Server r2008 and C#?

I have both a local server and remote server that has an identically structured table in each. In the local server I have a database table which is inserted, updated regularly. There is no primary key in the table

Once a week I want to copy the data from the local table to the identical table in the remote server.

Please help me to find the solution.

Upvotes: 1

Views: 5135

Answers (4)

yurart
yurart

Reputation: 603

The easiest way if tables are identical is:

INSERT INTO table1
SELECT * FROM table2 
where <some condition>

Upvotes: 0

Ron.B.I
Ron.B.I

Reputation: 2766

I believe what you are trying to do is a Mirror database, updated weekly, if that is the case:

Using Database Mirroring is a best practice instead of manually doing this yourself, I suggest you read about Mirroring here:

Database Mirroring

And afterwards follow this guide:

Setting Up Database Mirroring

your local server should be the principal and your remote will be the mirror

Edit:

Just to be clear, I highly recommend this approach, it will give you benefits such as automatic failover (when your local server crashes it will use the remote one) , you can read all about the benefits in the links above so I won't carry on and on about it, just emphasize that is recommended.

Upvotes: 2

Vamsi Pamula
Vamsi Pamula

Reputation: 147

Once Try this..

INSERT INTO DestinationTable (Column1,Column2,Column3)
SELECT Column1,Column2,Column3 FROM   SourceTable

Upvotes: 1

JanR
JanR

Reputation: 6132

Since these are both existing tables you would do something to the extend of the following:

    INSERT INTO [DATABASE IP].databasename.schemaname.tablename
    (
    column1
    ,column2
    ,column3
    ,column4
    ,column5
    ,column6
    )
    select 
    column1
    ,column2
    ,column3
    ,column4
    ,column5
    ,column6
    from tablename

    where --some condition here, maybe on date?

For this to work you will need to make sure that you have the same login name in both databases both local and remote as well as the same password in both.

Upvotes: 1

Related Questions