We're working on a bulk import tool and hoping that we could create and update content manually and directly using SQL queries. The document types have already been created and we want to make sure that when inserting or updating, all the proper tables are affected. If there is a specific order the sql queries need to run in for inserts vs. updates, that knowledge would also be appreciated.
We already know that we'll need to create entries in the following tables:
umbracoNode
cmsPropertyData
cmsContent
cmsContentXML
cmsDocument
cmsContentVersion
cmsPreviewXml
What would help the most are examples of successful INSERT (for new content) and UPDATE (for existing content) queries.
I know it is not a real answer on your question, but if you can, abandon this idea.
Write a UmbracoApiController and then use the ContentService to create and save document. It will take care of all the needed updates. This will take care of updating the cache and the xml cache. If you only perform SQL this won't be done automatically.
If you really (really really) need to update the SQL, I would verify your workflow against the Umbraco source code of the content Repository.
I would NEVER use direct SQL for bulk import you will get in trouble because of conflicting id's etc. Use ContentServer as Damian said or shameless plug use CMSImport http://soetemansoftware.nl/cmsimport to do the task.
Please correct me if I'm wrong, if I understand the cmsimport tool correctly it presupposes that the data will be for one document type at a time. Our import template includes multiple document types per row. With sql, you can query the original import table for the data related to each doc type and update the table with created ids as you go. I am not sure how cmsimport will handle checking, updating and creating multiple document types at the same time (with one flat file).
I've used the content service before just had some concerns about handling so much data effectively. I'm guessing we'll still use sql to stage everything and use .net to push the content inserts/updates.
Our import file includes multiple document types per row, so it's not just a "take file, loop through and create content" sort of operation. We need to have something as a staging ground to create each piece of content and then relate it all together as necessary.
Manually create content using SQL
We're working on a bulk import tool and hoping that we could create and update content manually and directly using SQL queries. The document types have already been created and we want to make sure that when inserting or updating, all the proper tables are affected. If there is a specific order the sql queries need to run in for inserts vs. updates, that knowledge would also be appreciated.
We already know that we'll need to create entries in the following tables:
What would help the most are examples of successful INSERT (for new content) and UPDATE (for existing content) queries.
Thanks, Simon
I know it is not a real answer on your question, but if you can, abandon this idea.
Write a UmbracoApiController and then use the ContentService to create and save document. It will take care of all the needed updates. This will take care of updating the cache and the xml cache. If you only perform SQL this won't be done automatically.
If you really (really really) need to update the SQL, I would verify your workflow against the Umbraco source code of the content Repository.
As Damiaan says you can use the content service to do this.
If you want to save on development time you can use https://our.umbraco.org/projects/developer-tools/cmsimport/ to run your import.
This is a commercial package. But it might fit your needs and the license cost isn't that big
Dave
I would NEVER use direct SQL for bulk import you will get in trouble because of conflicting id's etc. Use ContentServer as Damian said or shameless plug use CMSImport http://soetemansoftware.nl/cmsimport to do the task.
Best,
Richard
Please correct me if I'm wrong, if I understand the cmsimport tool correctly it presupposes that the data will be for one document type at a time. Our import template includes multiple document types per row. With sql, you can query the original import table for the data related to each doc type and update the table with created ids as you go. I am not sure how cmsimport will handle checking, updating and creating multiple document types at the same time (with one flat file).
Indeed CMS import is a alternative too. It'll save you a lot of time.
Also: you can not do this with only SQL in any effective way, caches will be outdated and are difficult to refresh correctly after the fact.
It sounds like the overwhelming opinion is to use the content service or possibly implement cmsimport.
It is! :-)
I've used the content service before just had some concerns about handling so much data effectively. I'm guessing we'll still use sql to stage everything and use .net to push the content inserts/updates.
What does "use sql to stage everything" mean?
Our import file includes multiple document types per row, so it's not just a "take file, loop through and create content" sort of operation. We need to have something as a staging ground to create each piece of content and then relate it all together as necessary.
Okay, as long as you're not trying to use SQL to create Umbraco content by inserting data in the Umbraco tables.. :-)
is working on a reply...