Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravijangra
Starting Member
1 Post |
 Posted - 2004-10-26 : 11:16:02
|
| HiPlease go through the below mentioned scenario:I am having two SQL databases say(A and B), same schema. There has been some extra data in database A. Now the purpose is to migrate that particular data from database A to database B.Using DTS to transfer all tables data to database. There is some indexing on some field for each table.As the source and destination tables are having some same data, Following error is displayed-CAn not insert duplicate key row in object *** with unique index <index_name>.I just want to copy the additional data in database A to database keeping the data in B as intact.Is there any workaroud for this issue.Any type of help is appreciated.Regards,Ravi |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-10-27 : 04:17:36
|
| OK. How many tables you want to copy from database A to B ?. Are you sure that database B has some data which is not in the database A ? I think we can use T-SQL to find the missing records and insert them ! Otherwise we can use DTS to transfer the data by droping & then inserting the records on the destination table.- Sekar |
 |
|
|
rav_hi
Starting Member
4 Posts |
Posted - 2004-10-27 : 04:28:43
|
| Hi Sekar,I am supposed to transfer data from more than 600 tables, I was just quoting one example. Further I have to keep all the data in database B. Tables in database B may or may not have some data from the tables of data A. In addition to that tables in database have some additional data also.Can you please provide sample T-sql to insert the diff data for a table.I was just trying the following Query to insert the diff data for a table table_case:-----insert into table_case {(select * from [or21_src].dbo.table_case) - (select * from table_case)};While executing the query I am getting the following error-[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.If you have any clue then do provide.Thanks,Ravi |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-10-27 : 04:49:32
|
| USE LEFT JOIN.. This join should return all the rows from table, database B. where condition will filter out the records not in database A. Use this records to insert from database B to A!! Select B.id, A.id from databaseB.dbo.table B left join databaseA.dbo.table A on B.id = A.id where A.id is null- Sekar |
|
|
|
rav_hi
Starting Member
4 Posts |
Posted - 2004-10-27 : 04:57:19
|
| Thanks Sekar,I'd try and let you know the results.Ravi |
|
|
|
|
|
|
Unique index error while Copying duplicate data