Solved

Import Table data between database

Posted on 2014-02-12
8
218 Views
Last Modified: 2016-02-10
Hello there,

I have 2 database one at my place and another on the client. the client enter data in the database and I want to update my database with their data. How can I do this with SSIS.

cheers
Zolf
0
Comment
Question by:zolf
  • 3
  • 2
  • 2
8 Comments
 

Author Comment

by:zolf
ID: 39852717
I managed to use SSIS and connect to the client database and create the package but I get this error when I run the package. I know the cause of the error i.e. I have my own test data in the Branch table. How can I over write my table data with the client's.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E2F  Description: "Violation of PRIMARY KEY constraint 'PK__Branch__3213E83F7F60ED59'. Cannot insert duplicate key in object 'dbo.Branch'.".
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 200 total points
ID: 39852832
Ok, for doing this approach, what I would suggest is to drop all the data in your database, before importing it from the client..

Add a SQL task before the data flow and add the statement

TRUNCATE TABLE <your table Name>
0
 

Author Comment

by:zolf
ID: 39852838
thanks for your comments.
But is there no way to only import some tables
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39852905
the problem you are facing now, is not with the import but with the constraints on your existing table...

So, if you have duplicate data in the table, the Primary key will get violated and an error will be thrown...
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 300 total points
ID: 39855492
But is there no way to only import some tables

Sure there is.  But if they're linked to each other through FK/PK (Foreign Key/Primary Key), whenever you import a table with a FK you'll also need to import the table with the PK because otherwise the values in the FK don't make any sense.

Can you explain a bit more what your end goal is?  Do you just need a copy of the database?  In that case I'd consider backup/restore instead of data transfer...
0
 

Author Comment

by:zolf
ID: 39860926
ValentinoV

thanks for your comment.I am implementing a software for my client. I do testing on my machine database and then I let the cleient enter the proper data into those tables.Now my problem is I don't want to replace/restore the full database. I just want to replace a table data which my client has filled with proper data. Hope I made myself clear.

cheers
ZOlf
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 300 total points
ID: 39864219
Okay, well in that case you'll have to transfer the related tables too, that's the only clean way to get rid of that error.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question