StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhich approach should I use for generating + inserting 100+ records into a SQL table
text
Body
copied!<h2>Some facts about my data</h2> <ol> <li>I have a <code>TablePattern</code> that defines periodic day pattern (usually long from few days and up to 28)</li> <li>Day pattern is stored as <code>varbinary(4)</code> as bit flags. Some days are on (set to 1 == <em>true</em>), others are off (set to 0 == <em>false</em>). There's also <code>Length</code> column that defines pattern length stored in the binary column</li> <li>Each pattern record also has information whether weekends and holidays nullify pattern settings (explained later)</li> <li>I have table <code>Holidays</code> that defines holidays (some repeatable others fixed on a particular year)</li> <li>I have <code>TableCalendar</code> where I need to create X number of records. One for each day.</li> </ol> <h2>The problem</h2> <p>When generating records</p> <ol> <li>I know which pattern to use</li> <li>I know date range of days whose records I have to create</li> <li>Along with pattern I also know whether weekends/holidays take presence over pattern settings - this means that when I will be generating records it will automatically set them to <code>false</code> even though pattern says it should be <em>true</em></li> <li>I also have additional <code>Offset</code> that tells me how to apply the pattern to my date range - if <code>offset == 0</code> pattern starts at the first day; <code>offset == 3</code> pattern starts on fourth day; offset is <strong>always</strong> between 0 and pattern length less 1 (in range of pattern length modulo values)</li> <li>all days before offset also have to be set as if pattern has started earlier (before starting day of the date range)</li> </ol> <h2>Questions</h2> <p>I have basically two options:</p> <ol> <li>I can generate calendar data on the middle tier, which would make it simpler, but would require a lot of DB calls to insert each record into <code>TableCalendar</code>.</li> <li><p>I can write a stored procedure and provide it:</p> <ul> <li>date range</li> <li>pattern record ID</li> <li>offset</li> </ul> <p>and then write all the logic that would generate and insert records into <code>TableCalendar</code>. I can't even imagine how to write this complex stored procedure that would use binary pattern and apply it with offsets etc.</p></li> </ol> <p>Date ranges will usually be 1, 3 and 12 months long the second one being the most frequent one.</p> <p>And since this generation is part of a web application's Ajax call it has to be as snappy as possible. That's the only reason I suppose that generating all records with a singe DB call would be faster than making lots of DB calls and execute some extremely simple insert statement. But I'm pretty sure that SP will become quite complex hance can also become sluggish.</p> <p><strong>What would you suggest I should do?</strong></p> <p>You can as well provide some TSQL code that would show how to generate those records using data I provided in a stored procedure.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload