Append data from one sharepoint list to another

[muttnut]

We are in the process of consolidating 2 teams, each of whom had their own intake form in SharePoint. We would like to take the data from one of the SharePoint lists and append it to the other. Of course, the fields aren't the same, so at the very least names need to be changed. I've tried doing it via the links to MS Access and just running an append query but where we run into issues are with the various names in the lists...they appear to be names but are stored as numeric values linked to another table called UserInfo. Each data table has it's own accompanying UserInfo table so the ID numbers will not match. If we can get this to work, the idea is, at least for a while, to have this be an ongoing process where data from one table loads to the other table every night. Anyone have an idea on how to move this data short of my re-entering 2000 records manually?

[computerfox]

Have you tried a query something like:

insert into sp_list2 (sp_list1_col1,sp_list1_col2) select sp_list2_col1,sp_list2_col2 from sp_list2;  
 

I think the bigger question is how would you automate this since it's recurring every night? I believe PowerShell can automate this.

[muttnut]

Yes, I tried that query but I either run into conversion errors trying to insert a text into a number or a lock error trying to use a PK from the sp_list2 in the sp_list1 that's already been used. I'll look into PowerShell--thanks for your help!

[computerfox]

Are one of the columns a primary key, then? Oh boy...
The cleanest and easiest method I think would be to make a new table call it sp_list_merge and then you can have columns ID, ID_LIST1, ID_LIST2. The actually primary key for the new table would be the new ID column and you can keep track of the ID's from list1/list2. Would this be something you'd be willing to implement? When dealing with two lists, it's not unheard of to have a third list for merging purposes.

[muttnut]

This is an idea that's been rattling around in my head. I've got the manager of one of the teams agreed to this compromise...we'll have to see if I can get the other to agree (of course, the alternative would seem to be you can't get your data merged, so you would think it would be an easy decision!) Thanks for taking the time to reply!