Solved

Import Table data between database

Posted on 2014-02-12
8
215 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now