I would upload the transaction IDs with the new date value in a temporary table, add a join between the transaction table and the temporary table, and update the transaction table with the new values from the temporary table.
Although it seemed relatively simple to update thousands of records with temporary tables, what if I had thousands of transactions that needed multiple field values updated?
Second, you create a stored procedure with a table-valued parameter, as Listing 2 shows. Callouts A through E highlight the steps that show you how to access table-valued parameters.
Third, you execute the stored procedure, as Listing 3 shows. (If you’re new to application development, the callout steps will help get you started.) The code at callout A in Listing 4 adds three assembly references.
First, with the code segment below, you create a new user-defined table type.
Second, you create a stored procedure with a table-valued parameter, as Listing 2 shows.
Figure 1 shows the results of the insert statement in Listing 3. The code at callout B declares the Data Table and sets the Data Grid View as this Data Table.
Listing 2: Code to Create Stored Procedure --Create procedure to update many rows CREATE PROCEDURE \[dbo\].\[Update Transaction Dates\] @Table Param Change Trans Date READONLY AS UPDATE b SET b. New Date FROM @Table Param a JOIN Trans Log b ON b. Trans ID Listing 3: Code to Execute Stored Procedure --Add the table-value parameter and call the stored procedure DECLARE @New Table Param Change Trans Date INSERT INTO @New Table Param(Trans ID, New Date) VALUES (101,'1/10/2009'),(102,'2/10/2009'), (103,'3/10/2009'),(104,'4/10/2009'),(105,'5/10/2009') EXEC dbo. In the namespace of your application, create a private datatable.
Suppose, for example, that thousands of transactions needed their transaction dates updated.Because transaction logs are not recorded for table-valued parameters, performance improves.Previously, when multiple values were involved, the insert statement was executed multiple times; now, I use a table-valued parameter—with numerous values—that’s treated as one value and requires only one insert statement.Change Trans Date AS TABLE( Trans ID varchar(36) NOT NULL, New Date datetime NOT NULL) INSERT INTO Trans Log VALUES(1,'8/8/2009',1) INSERT INTO Trans Log VALUES(2,'8/8/2009',2) INSERT INTO Trans Log VALUES(3,'8/8/2009',3) INSERT INTO Trans Log VALUES(4,'8/8/2009',4) INSERT INTO Trans Log VALUES(5,'8/8/2009',5) The three steps that follow, each with its code segment, let you see how to create and execute table-valued parameters in T-SQL. IO; # END CALLOUT A namespace matthewharrisapplication \ # END CALLOUT E As you can see in Listing 4, using a .
First, with the code segment below, you create a new user-defined table type. NET application makes it simple to access table-valued parameters.
You can also see how easy it is to add this functionality to an application; doing so lets others update the transactions themselves, rather than sending a request to the DBA.