?
Solved

Import Table data between database

Posted on 2014-02-12
8
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

771 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