Automatically copy data from SP2010 external list to an SP2010 custom list

Question

I have an SP 2010 external list that is populated with customer names. The list is updated occasionally throughout the day. I would like to automatically copy the newly added names to another SP 2010 list when it is updated or at a set time (hourly).

Is there an easy way to do this? And if not, is there at least a way to do it?

Thank you for your help.

score 6 · Answer 1 · answered Apr 21 '11 at 06:01

Unfortunately, External Lists do not support workflows. So workflow is not a solution here.

One of the way to do this would be to create a custom timer job to synchronize items & configure to run it periodically. See details about how to create & register custom job here.

But this approach has it's own drawbacks:

it is complex enough
you will need a farm scoped feature + receiver to register the job. The reason is that for security purposes you can't register a custom job from within the code running in Content Web application (so it will not work in site collection level feature receiver), only from code running in Central Admin app.

score 2 · Answer 2 · answered Apr 21 '11 at 10:48

I'd build a windows service or a timed job in SharePoint and then hook up a compatible ado.net connector to my process. This way you can copy or synchronize data between your two lists as if they where ordinary SQL tables.

private void example()
{    
    // Fetch data from your left sharepoint
    SharePointConnection leftConnection = new SharePointConnection(@"
        Server=mysharepointserver.com;
        Database=mysite/subsite
        User=spuser;
        Password=******;
        Authentication=Ntlm;
        TimeOut=10;
        StrictMode=True;
        RecursiveMode=RecursiveAll;
        DefaultLimit=1000;
        CacheTimeout=5");

    leftConnection.Open();

    string leftQuery = "SELECT * FROM LeftList";
    SharePointDataAdapter adapter = new SharePointDataAdapter(leftQuery, leftConnection);

    DataTable dt = new DataTable();
    adapter.Fill(dt);


    // Insert data in right sharepoint
    SharePointConnection rightConnection = new SharePointConnection(@"
        Server=anothersharepointserver.com;
        Database=whateversite
        User=spuser;
        Password=******;
        Authentication=Ntlm;
        TimeOut=10;
        StrictMode=True;
        RecursiveMode=RecursiveAll;
        DefaultLimit=1000;
        CacheTimeout=5");

    rightConnection.Open();

    // build your rightQuery here
    string rightQuery = "Insert into"...

    SharePointCommand command = new SharePointCommand(rightQuery, rightConnection);
    command.ExecuteNonQuery();

}

You could try this one http://www.bendsoft.com/net-sharepoint-connector/. This ado.net connector uses the API of SharePoint so you can run the service in a third machine and as long as it has access you'll be fine.

There are some examples and howto's at http://blog.bendsoft.com

score 0 · Answer 3 · answered Nov 28 '12 at 21:11

I'd use MS access in order to copy data out of one table, and push it into another table. I've used Access/Sharepoint extensively, and it's a little bit buggy.. but it generally works. It might crash- but for 90% of small tables, heck yes you can do this!

score 0 · Answer 4 · answered Apr 21 '11 at 04:27

A workflow can be created for this easily. The workflow can be triggered when an item is newly created or updated. The workflow can create the same item into another list.

Try creating the workflow using SharePoint designer, which is quiet straight forward.

Hope this helps. :)

score 0 · Answer 5 · answered Apr 22 '11 at 15:20

0

Why do you want to copy the external data to another list? What is the purpose of doing this? Perhaps there is a better solution for what you are trying to do.

The reason I say this, is because you are going to be replicating data and could get a bit tricky, especially if you allow the data to be updated in both places.

answered Apr 22 '11 at 15:20

Doug Stalter

733
4
11

Hi Doug - Basically we have an EMR system that was designed 30 years ago for a paper world. The system works well for keeping patient data, but cannot be used for tracking patient progress through the system. It's very limited, cannot be altered and really the only option.Fortunately, the database can be easily accessed, and we're using it to seed new patient information into a SP tracking system we've developed. We're not replicating data in both locations, only taking the most basic information to use in another. We could re-type the patient information, but this far more efficient. – Ben Apr 24 '11 at 19:52
Hi Ben, it sounds like you could leverage BCS to bring the data into SharePoint and use it for what you need. – Doug Stalter Apr 25 '11 at 15:26

Automatically copy data from SP2010 external list to an SP2010 custom list

5 Answers5