Hi,
I need to transfer data from one table to the same table in another server which has been truncated. Can somebody please, let me know the easiest way to do it. Please, help me.
Thanks, Chris
A:
Are you simply bulk-duplicating all records, including PKs? Please post schema - it will affect which technique to use.
le dorfier
2008-12-20 07:27:18
+2
A:
Use the SQL Server Import and Export wizard. It's probably the easiest way to accomplish this task.
For more advanced data transfer, consider using bcp utility, BULK INSERT statement and OPENDATASOURCE.
Mehrdad Afshari
2008-12-20 07:30:38
Bear in mind the import/export wizard is seriously borked (at least in SQLServer 2005). We had to stop using it because it didn't maintain foreign key relationships with auto-enerated keys.
cletus
2008-12-20 08:39:19
A:
I would use Data Transformation Services (aka Integration Services).
Andy Webb
2008-12-20 07:32:26
Way overkill. He should learn a whole nother software package to load a file?
le dorfier
2008-12-20 07:37:28
DTS is totally easy to use - also it comes with SQL server so it's not exactly "another software package"
1800 INFORMATION
2008-12-20 07:44:43
+5
A:
Setup linked servers and then use the following on the destination database:
INSERT INTO existingTable (col1,col2..)
SELECT col1,col2...
FROM linkedserver.dbo.database.othertable
Nick Kavadias
2008-12-20 08:20:14
Also remember to consider identity columns, SET IDENTITY_INSERT ON and OFF afterwards. Also check DBCC RESEED as you may not be using IDENTITY(1,1). You were able to truncate, so there must be no foreign keys, but there may be constraints - Check and work with as appropriate.
Meff
2008-12-21 12:48:04
+2
A:
Back up the table on the one server, to a file, and restore that file into the empty table on the other one...
Charles Bretana
2008-12-20 17:06:16
this is the best answer. also, more specifically, use the "generate sql" script to export the table data to a comma delimetted file.
djangofan
2009-07-24 20:31:56
A:
Depending on the amount and frequency of your data transfer. It it's a low volume one time process, you're better off with using T-SQL to directly insert the data. This can be done either through linked servers or OPENQUERY clause.
If its high volume one time process, use SSIS or BCP utility.
If its high volume high frequency, use replication.
Nai
2009-07-02 09:42:08