Tech Off Thread

14 posts

CLR Stored procs and INSERTs

Back to Forum: Tech Off
  • vbrunner__

    this thread provoked my question:
    also, i apologize if this is nothing but a demonstration of my newbie-ness in SQL...

    am i the only one that thinks a series of INSERT statements to submit many rows of data is a little insane?
    say i want to add 100 new authors to a table; i'd much rather call some sort of stored procedure to do this all in one fell swoop then to make 100 round trips to the DB.

    that's where CLR Stored Procs come in.  i've never tried them, but from what i know this will allow me to just pass a List<SomeObject> to the database to preform all the inserts right on the server, correct?

  • vbrunner__

    well that's hardly the ONLY way.  where i work one of thier methods is just a really long string split up into chunks the size of the field with a * at the end.  it works quite nicely, actually, but it's still a hack.

  • figuerres

    vbrunner__ wrote:
    this thread provoked my question:
    also, i apologize if this is nothing but a demonstration of my newbie-ness in SQL...

    am i the only one that thinks a series of INSERT statements to submit many rows of data is a little insane?
    say i want to add 100 new authors to a table; i'd much rather call some sort of stored procedure to do this all in one fell swoop then to make 100 round trips to the DB.

    that's where CLR Stored Procs come in.  i've never tried them, but from what i know this will allow me to just pass a List<SomeObject> to the database to preform all the inserts right on the server, correct?


    Generally you do not want "Bulk inserts" in a database due to things like the relations between tables that need to be updated. also if a row violates a constraint of some kind you need to have a way to handle it... imagine that 100 rows and the last 3 rows are no good....
    so you try to insert 100 rows and the 97th row is rejected....
    whups !
    if that insert is a single "unit of work" then it has to reject all the inserts rolling back to the state before the inserts started...
    so then what do you do? try 50 and see if they work? then try the next 50 ?

    that would be very messy and wastefull...

    but when you do need to do a big load there have always been tools like BCP (Bulk copy) and there are some t-sql things that support some forms of bulk inserts.

  • figuerres

    BULK INSERT 
       [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
          FROM 'data_file' 
         [ WITH 
            ( 
       [ [ , ] BATCHSIZE = batch_size ] 
       [ [ , ] CHECK_CONSTRAINTS ] 
       [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
       [ [ , ] DATAFILETYPE = 
          { 'char' | 'native'| 'widechar' | 'widenative' } ] 
       [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
       [ [ , ] FIRSTROW = first_row ] 
       [ [ , ] FIRE_TRIGGERS ] 
       [ [ , ] FORMATFILE = 'format_file_path' ] 
       [ [ , ] KEEPIDENTITY ] 
       [ [ , ] KEEPNULLS ] 
       [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
       [ [ , ] LASTROW = last_row ] 
       [ [ , ] MAXERRORS = max_errors ] 
       [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
       [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
       [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
       [ [ , ] TABLOCK ] 
       [ [ , ] ERRORFILE = 'file_name' ] 
            )] 

  • scott976

    The only way to do multiple inserts in a stored procedure is to use a comma delimited string.

  • scott976

    Okay vbrunner using a * as your delimeter is not any different than a comma.  I heard another way to do it is use ~ as your delimeter.

  • lothan

    No, it isn't. You can certainly use delimited strings, but you must ensure the character you use as a delimiter is not use in any of the fields. The code is also quite a bit messy and may be slow since it requires quite bit of string manipulation.

    An alternative approach to insert multiple rows in SQL Server 2000 and later is to pass XML data sets to the stored procedure using some code like this:

    CREATE PROCEDURE blah (@XmlDoc varchar(MAX))
    AS
    DECLARE @XmlDocId int

    EXEC sp_xml_preparedocument @XmlDocId OUTPUT, @XmlDoc

    INSERT INTO [Table Name] ...
    SELECT ...
    FROM OPENXML(@XmlDocNumber, '/root/namespace', x)
    WITH (...schema definition ...) AS [Alias Name]

    EXEC sp_xml_removedocument @XmlDocId

    The syntax may be easier with SQL Server 2005, but this is the basic technique we used with SQL Server 2000 in a few instances.

  • kidzi

    Well, if you imply that you each insert involves a roundtrip to the server, then there are other ways that are much cleaner which do not require a 1:1 for roundtrips.  Datasets for example, if you have a table of values, then in the data adapter, you can change a field for a batch size and .net will actually send in the package multiple insert statements (50, 100, whatever you want) at a time for SQL to process.  That is the easiest way.  More complicated ways involve like what others say, send an Xml Document and parse, or make a function to split a delimited string and use that to slice and dice. 

    Generally for large imports or transfers of data, there is a whole area of Sql Server, SSIS which handles that much more efficiently.  Generally you wouldn't send that much data over a single user's transaction through a website / client interaction.  Not that it wouldn't happen, it is just unlikely in most cases.  The big transfers tend to happen in batch processing where SSIS can be used, or Bulk Import (BCP).

  • ScanIAm

    You can certainly come up with ways to bypass the 1 insert per row, but the tradeoff is that if any of the inserts fails, you lose the whole batch.  (note: I've never used BATCH INSERT, but I think this still holds).

  • master_key

    For me XML bulk update and update through Dataset is same.Undelying dataset is also using XML only thing is that it is more sophosticated and tightly coupled with schema.

  • Pace

    ScanIAm wrote:
    

    You can certainly come up with ways to bypass the 1 insert per row, but the tradeoff is that if any of the inserts fails, you lose the whole batch.  (note: I've never used BATCH INSERT, but I think this still holds).



    BEGIN TRANSACTION is my favourite statement of all time... closely followed by COMMIT Tongue Out

  • kidzi

    ScanIAm wrote:
    

    You can certainly come up with ways to bypass the 1 insert per row, but the tradeoff is that if any of the inserts fails, you lose the whole batch.  (note: I've never used BATCH INSERT, but I think this still holds).



    Generally, but the way i mentioned with the dataset and the multi-row sending it at a time over the network does not work that way. It's not a true BCP type of insert though, it just removes the network latency by putting them into fewer packets - but you have full control, at the .net layer, for what happens if a particular row fails, or if you need to return identities and stuff, it's fairly transparent but is an easy optimization win.

    Kevin

  • JChung2006

    You could use the SqlBulkCopy class instead of CLR stored procedures to populate a SQL Server database from C# data structures.

  • vbrunner__

    scott976 wrote:
    Okay vbrunner using a * as your delimeter is not any different than a comma.  I heard another way to do it is use ~ as your delimeter.


    how is that at all necessary?  i was only providing anecdotal evidence of that pattern in my company.  the relevant part of that comment was the "it's still a hack" part.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.