Copy records from one table to samme table in another sql server

[Lido98]

HI
I am very pleased to join this forum

I am new to SQL programming , I am facing a challenge
I need to copy the data from one table in one sql server to the same table in enother SQL Server

From Server A table (Employee) To Server B Table (Employee)
Can anybody suggest which way is the best to do this

Thanks in advance.

[darkagn]

Hi Lido98 and welcome to DaniWeb :)

This can be done in SQL Management Studio in a few easy steps:

• First of all, you need to connect to Server B.
• Once connected, you need to link Server A to Server B. In SQL Mngmt Studio 2005, this can be done by right-clicking Linked Servers under the Server Objects field and adding the new linked server.
• Then you need to run a query similar to the one below.

INSERT INTO ServerB.DatabaseName.dbo.Employee
(SELECT * FROM ServerA.DatabaseName.dbo.Employee)

Where ServerA is the server name of Server A, similar for ServerB and DatabaseName is the name of the databases on each server. I should also point out that if the Employee table has an auto increment field and you want to preserve the order of this field, you need to set auto increment off for the ServerB Employee table before you run the insert and reset it to on when you have finished. Let us know if you need to do this and we'll show you how.

Anyway, hope this helps :)
d

[Lido98]

Hi

Thank you for the suggested solution

I will Try it on monday and get back.

cheers.

Hi Lido98 and welcome to DaniWeb :)

This can be done in SQL Management Studio in a few easy steps:

• First of all, you need to connect to Server B.
• Once connected, you need to link Server A to Server B. In SQL Mngmt Studio 2005, this can be done by right-clicking Linked Servers under the Server Objects field and adding the new linked server.
• Then you need to run a query similar to the one below.

INSERT INTO ServerB.DatabaseName.dbo.Employee
(SELECT * FROM ServerA.DatabaseName.dbo.Employee)

Where ServerA is the server name of Server A, similar for ServerB and DatabaseName is the name of the databases on each server. I should also point out that if the Employee table has an auto increment field and you want to preserve the order of this field, you need to set auto increment off for the ServerB Employee table before you run the insert and reset it to on when you have finished. Let us know if you need to do this and we'll show you how.

Anyway, hope this helps :)
d

[Lido98]

Hi again

I have a 2 small issues regarding this matter

Actually the table in server B is true copy of the table in server A , but Table A has increased and now we have to copy the new records to Table B, the old records will også be copied right.

Is there any way to avoid inserting the duplicates like this

INSERT INTO ServerB.DatabaseName.dbo.Employee (avoiding inserting duplicates)

(SELECT * FROM ServerA.DatabaseName.dbo.Employee)

ot do I have to delete the duplicates after inserting in Table B, is there any SQL command to delete the Duplicates.

if not I suppose I can achieve that by selecting distinct entries to a temp table , delete the records in table B and inserting the records back from temp table to table B.but need a code to do that.

Other issue is that I want to take a backup of table B before doing any thing, how can that be done.

once again thank you and plz bear with me.

[darkagn]

Hi again,
The easiest way to avoid duplicates is to limit the select statement according to some criteria. Do you have an ID field in the table that you can use, or some sort of date field to specify when the object was inserted? ID is easiest:

INSERT INTO ServerB.DatabaseName.dbo.Employee B
(SELECT * FROM ServerA.DatabaseName.dbo.Employee A
WHERE B.ID NOT IN (SELECT ID FROM A)

but date can also be done:

INSERT INTO ServerB.DatabaseName.dbo.Employee B
(SELECT * FROM ServerA.DatabaseName.dbo.Employee A
WHERE B.DATE > (SELECT MAX(DATE) FROM A)

Let me know if you don't have such a structure in this table (maybe describe the primary key for me please?)

To backup the database first (which is ALWAYS a good idea btw), in SQL mngmnt studio 2005, right-click the database and select Tasks from the popup menu and select Back Up from the submenu. You need to check the settings in the screen that appears, pretty much the only thing I ever change is the destination. Press the Backup button and watch the magic unfold!

HTH,
d

[Lido98]

Hi Again

I triede to insert data form the old table into the new table , but I had problem linking the 2 servers , then I used the file functions , by saving all the old data into a csv file and inserting it through this command

BULK INSERT new_table
    FROM 'C:\oldserver\olddata.CSV'
    WITH 
    ( 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

so far so good , then to get rid of the duplicates I used the follwing blog
How to Identify and Delete Duplicate SQL Server Records

- http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx.

Now I have the old and new data in the same table withount duplicates , but I stil have a small problem , I have to delete all the entries created before 01-01-2008, However the problem seems to be simple , but it is not becuase the type of the creation date field to be compared is varchar (I do not know why ), hence the comparation < 2008.01.01 does not return the right set of data

How can I use or convert this field in this way - delete from new_table where creationdate < '2008-01-01'

Thank you very much.

[darkagn]

What is the type for the field 'creationdate'? Is it a date, datetime, smalldatetime or something else?

[Lido98]

Hi

The requirement of deleting old entries is not considered any more

my problem has been solved

Thansk for the good help.