Moving Sharpooint list item from one list to another using T-SQL

[SACRob]

I am attempting to move sharepoint list items from a main list to an archive list. I am using WSS 3.0 Both of these lists were created from the same template so the column structure matches perfectly.

I have attempted to do this migration my altering two sql tables. alldocs and alluserdata
in the alluserdata table i have modified two columns TP_ListID and TP_DirName.
in the alldocs table i have modifed the same two columns TP_ListID and TP_DirName.

after making these modifications to an item in the main list they do not show up in the archive list. i have compared the records side by side with an item that was manually created in the archive list and i cannot see any differneces between a list item created in sharepoint to one that i manually move in this fashion.

I feel as tho i am missing a table that needs to be changed in a similar fasion. I know i can use a datasheet view and copy and paste the records however i am attempeting to maintain the version history between the two lists. The reson i am choosing t-sql is i would like to schedule a job to archive these records once a month as the main list is taking several minutes to load due to the amount of records in the list.

 

[ehaze]

Why are you doing it in the SQL Tables?

You can use SharePoint Designer Workflow, Visual Studio Workflow, PowerShell, SSIS (SQL Server Integration Services).

In all the years I have been working with SharePoint, I've never once had to move data by digging into the SQL Tables.

http://www.sharepointed.com/

 

[SACRob]

Thank you i was able to create a workflow in sharepoint designer to do this very task however i am running into some trouble with the amount of data in the list.

I cannot easily run the workflow on every item in the list. I have been trying the onchange event and making a small change to the list items in datasheet view but it is constantly crashing my web browser. any suggestions?

 

[ehaze]

How many items in the List?
Type of column(s) you are trying to update?

Have you tried updating a smaller data set?

http://www.sharepointed.com/

 

[SACRob]

We have 60550 items in the list with up to 4 versions each with an attached pdf file so 204753 rows with version history. I can modify about 10 at a time without IE crashing. The field I am updating is a text field that only occasionally has data.

I was going to try and leverage the site content and structure page however the office publishing feature is not enabled and my attempts to enable it have not been successful stsadmin errors out stating I am missing the publishing.xml in my hive folder..

I am debating on just moving this whole site to SharePoint 2010 where I can create some more advanced date based workflows. However with this much data I cannot export the template with data as it keeps erroring when it runs out of memory.

Thank you for your support so far you have been a great help.

 

[ehaze]

Sounds like updating 10 at a time would suck.

PowerShell is what I use when doing mass updates.

I can help with the script if you provide the logic.

After the mass update is done, you can then use the workflow maintain the List.

http://www.sharepointed.com/

 

[SACRob]

That would be great. I installed PowerShell on that webserver last night after reading your first post. It is a 2003 sp2 server so it was not an automatic update. I am relatively familiar with PowerShell in exchange 2010 however I am not sure what the module to import for sharepoint. I have seen a few already written scripts but I was not sure if they are backward compatible with SharePoint 2007 so I was afraid to run them being that this SharePoint site drives one of our largest accounts.

Not sure if it matters but I installed PowerShell on the web server not the sql server in the farm.

Thanks again.

 

[ehaze]

another thought....

Add a column to your List called Test1 (Single line of text).
Setup your workflow.
-workflow will run when item is added or updated.
Connect your List to MS Access.
-in Access, create an Update Query to write a value to Test1.


You can run the update on a small set of data by using the SharePoint ID field to filter the update.
Something like:
Update Test1 = "x"
Where ID between 64,000 and 65,000

Doing this would trigger the SharePoint Workflow to run.

http://www.sharepointed.com/

 

[SACRob]

the work flow logic is as follows.

AR list
If column [status] = "closed" copy to list ar - historical then delete from current list.

ar - historical list
if column [status] = "open" copy to list ar then delete from current list.

for the mass update if column [expense comments] is null then [expense comments]=1 else if [expense comments] is not null "1."+[expense comments]

 

[SACRob]

will i have an issue with the workflow if one list has the extra column and the other does not?

 

[ehaze]

adding a field wont mess with the workflow.

in the past when i have moved docs from library to another, i've lost the document history...

http://www.sharepointed.com/

 

[SACRob]

I created a new check box column and when doing the update query i'm getting an error. Error: Data cannot be inserted because there is no matching record.

my query is simple just
field name bit
table AR
Update true
criteria false

any suggestions i see online the linking from the userinfo table might be broken.. Ughhh this is such a pain. however what seems to be working well is i created a new view that just shows my column bit and i'm dragging the checkbox all the way down and its not crashing IE.

 

[SACRob]

Ohh and by the way this definitly removes history. However i spoke with the client and that is ok in this instance as performace comes before version history.

 

[ehaze]

when trying to update with Access, it's much easier to update a column type that is just simple text (single line of text).

http://www.sharepointed.com/