Where Is A Serial Number
Nancie Fazzari
I have a situation where I need to do an update on a very large set of rows that I can only identify by their ID (since the target records are selected by the user and have nothing in common other than it's the set of records the user wanted to modify). The same property is being updated on all these records so I would I like to make a single UPDATE call.
Would it make more sense to use individual update statements for each record and stick them into a single transaction? Right now I'm working with SQL Server and Access but,if possible, I'd like to hear more broad best-practice solutions across any kind of relational database.
Another alternative is to store those numbers in a temp table and use it in a join to do the update. If you are able to execute a single update statement is definitely better than executing one statement per record.
I would use a table-variable / temp-table; insert the values into this, and join to it. Then you can use the same set multiple times. This works especially well if you are (for example) passing down a CSV of IDs as varchar. As a SQL Server example:
Since you are using Access as a database, the number of ID's can't be that high. Assuming we're talking about less than, say 10,000 numbers and we should know the limitations of the containers to hold the ID's (what language is used for the front end?), I'd stick to one UPDATE statement; if that is most readable and easiest to perform maintenance on later. Otherwise I'd split them into multiple statements using some clever logic. Something like split the statement into multiple statements with in one, ten, hundred, thousand... ID's per statement.
But in my experience, it is quite often OK to leave this kind of optimisation to the database manager itself. The one thing that takes the most time is usually the actual connection to the database, so if you can execute all queries within the same connection it is normally no problems. Make sure you send off all UPDATE statements before you start to look into and wait for any result sets coming back though. :-)
We recently changed our system to limit the size of the in-clauses and always use bound variables because this reduced the number of different SQL statements and thus improved performance. Basically we generate our SQL statements and execute multiple statements if the in-clause exceeds a certain size. We don't do this for updates so we haven't had to worry about the locking. You will.
A single IN clause is very easy to understand and maintain. This is probably what you should worry about first. If you find that the performance of the queries is poor you might want to try a different strategy and see if it helps, but don't optimize prematurely. The IN-clause is semantically correct so leave it alone if it isn't broken.
Last of all, you don't have to worry about how Jet will process an IN clause if you use a passthrough query. You can't do that in code, but you could have a saved QueryDef that is defined as a passthrough and alter the WHERE clause in code at runtime to use your IN list. Then it's all passed off to SQL Server, and SQL Server will decide best how to process it.
The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).
Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.
Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.
Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
Displays date and time serial numbers as time values, according to the type and locale (location) that you specify. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
Allows you to modify a copy of an existing number format code. Use this format to create a custom number format that is added to the list of number format codes. You can add between 200 and 250 custom number formats, depending on the language version of Excel that is installed on your computer. For more information about custom formats, see Create or delete a custom number format.
We're expanding the max number for certain Number Sequences in our Dynamics AX 2012 R2 environment. We have several sequences that need to be updated but we first need to find out who the sequences are being used by. Does any one know how I can look up who/where the sequences is being used?
What's happening is that we have a few sequences that are about to run out of numbers to hand out. I need to find out who is using the sequences so I can contact the users and have them test the propose increase to the number sequences. For example if we increase the number of digits in a sequence those added values get printer on documented as 0's; place holders. I need to make sure that i don't add too many values where the the field on the report would run out of space.
I'm able to use sum to get the total score but I'm not sure how to get the number of criteria scored without using countif which Airtable doesn't seem to have.
Is there a way in AT to do what I'm trying to do? Any help is most welcomed.
But it's not coming up with the right answer. Based on the values in fields 5.1, 5.2, and 5.3 (image), the Max Possible Score: VTG field should be 6 not 9, because we're not counting the criterion that got scored -1 because the functionality isn't present.
Hi, I have a query based on pivot and dynamic SQL that results in the number of columns returned being unknown.
How can I use this within SSRS to produce a report with an unknown number of columns.
Not only is the number of columns unknown until the query has executed, the columns generated will have unique column headers.
Anyone got any ideas?
All the best
Duncan
Hi Thom, thanks for getting back to me so quickly.
I have tested the idea you suggest however it will not work in this particular situation and I will try to explain why.
The end-game here is to allow the user to export the data as a csv. Each row being a single instance with n number of columns. Exporting data from a matrix will not present the data in this way.
I think I may have to make the data available to the user in another fashion if it is impossible to dynamically create columns in SSRS
Thanks,
Duncan
I cannot see why a matrix will not do what you want.
Make the dataset have row number, column number (must be sequential starting at 1) and the value for the cell (if mixed datatypes convert them all to varchar)
The matrix will then output values where present, giving you rows with variable number of columns
Also if you want different headers per row (or group of rows) then add an extra column to identify header/data and sort appropriately.
I agree, I don't know why this wouldn't work using a Matrix; unfortunately we have no data to base off here though. The only thing I can think of is that you've not set up the Matrix correctly for your needs. David gives a pretty good description of what you need to do. based on the "idea" of some data.
If it doesn't help, provide some sample data, pre pivoting/dynamic SQL and then show what you want your dataset to look like; hopefully one of us will then be able to talk you through more thoroughly.
Thanks guys, your responses have given me food-for-thought.
One other idea I had been playing about with in my head is to stuff each complete row into a single column, separating each column value with commas. This would then provide a fixed data-set.
Once again thank you for your help.
All the best,
Duncan
A Known Traveler Number (KTN) is issued to all individuals approved to receive TSA PreCheck expedited screening. The KTN must be added in the KTN field when booking airline travel reservations to have the TSA PreCheck indicator appear on your boarding pass.
For members approved for the TSA PreCheck Application Program, this number is 9 or 10 digits long, can be a combination of numbers and letters and typically begins with TT for those who enrolled through IDEMIA, TE for those who enrolled through Telos, or AC for those who enrolled through CLEAR all of which are official TSA PreCheck enrollment providers.
For members approved for Global Entry, NEXUS, or SENTRI, the KTN is the CBP PASS ID number. This nine-digit number usually begins with 10, 13, 14, 15, 16, 50, 70, 80, 95, 98, or 99 and can be found on the back of your NEXUS, SENTRI, or Global Entry card in the upper-left corner or by logging on to the Trusted Traveler Programs website. Your trusted traveler card will not grant you access to TSA PreCheck lanes, instead you must add your PASS ID number in the KTN field when making airline reservations to ensure the TSA PreCheck indicator appears on your boarding pass.
Learn about moving your Verizon number and mobile service to another carrier. Find out how to protect your account from unauthorized transfers with Number Lock.
Note: Your Verizon account stays open until your last line's been moved and final bill issued. Find out how to cancel your Verizon mobile account.