Solved

Import Table data between database

Posted on 2014-02-12
8
226 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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