[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Import Table data between database

Posted on 2014-02-12
8
Medium Priority
?
239 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
7 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 800 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 1200 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 1200 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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