Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Simon 47 posts 107 karma points
    Sep 06, 2016 @ 19:38
    Simon
    0

    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:

    • 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.

    Thanks, Simon

  • Damiaan 434 posts 1279 karma points MVP 2x c-trib
    Sep 06, 2016 @ 21:27
    Damiaan
    0

    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.

  • Dave Woestenborghs 3217 posts 10834 karma points MVP 4x admin c-trib
    Sep 07, 2016 @ 06:38
    Dave Woestenborghs
    0

    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

  • Richard Soeteman 3660 posts 11196 karma points MVP
    Sep 07, 2016 @ 06:53
    Richard Soeteman
    0

    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

  • Simon 47 posts 107 karma points
    Sep 07, 2016 @ 14:22
    Simon
    0

    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).

  • Damiaan 434 posts 1279 karma points MVP 2x c-trib
    Sep 07, 2016 @ 08:23
    Damiaan
    0

    Indeed CMS import is a alternative too. It'll save you a lot of time.

  • Sebastiaan Janssen 4850 posts 14396 karma points MVP admin hq
    Sep 07, 2016 @ 08:28
    Sebastiaan Janssen
    0

    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.

  • Simon 47 posts 107 karma points
    Sep 07, 2016 @ 14:22
    Simon
    0

    It sounds like the overwhelming opinion is to use the content service or possibly implement cmsimport.

  • Damiaan 434 posts 1279 karma points MVP 2x c-trib
    Sep 07, 2016 @ 14:33
    Damiaan
    0

    It is! :-)

  • Simon 47 posts 107 karma points
    Sep 07, 2016 @ 14:38
    Simon
    0

    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.

  • Sebastiaan Janssen 4850 posts 14396 karma points MVP admin hq
    Sep 07, 2016 @ 14:40
    Sebastiaan Janssen
    0

    What does "use sql to stage everything" mean?

  • Simon 47 posts 107 karma points
    Sep 07, 2016 @ 14:42
    Simon
    0

    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.

  • Sebastiaan Janssen 4850 posts 14396 karma points MVP admin hq
    Sep 07, 2016 @ 14:50
    Sebastiaan Janssen
    0

    Okay, as long as you're not trying to use SQL to create Umbraco content by inserting data in the Umbraco tables.. :-)

Please Sign in or register to post replies

Write your reply to:

Draft