In previous versions of SQL Server, if I needed to update numerous rows of data, I used a temporary table.Suppose, for example, that thousands of transactions needed their transaction dates updated.
Update Transaction Dates @Table Param = @New Table Param --Select from the table to verify the change SELECT * FROM Trans Log Note: The storedprocedure_is the insert procedure that passes the table-valued parameter with the matthewharrisapplication. The code at callout C creates a parse CSV as a public list.
Book3.csv, which you can download, is an example comma-delimited file of the values that will be passed with the stored procedure. The code at callout D adds functionality to the Browse button.
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.
Finally, use the following code to verify that the values have been updated in the database. As you can see, it’s quite straightforward to use a .
Net application with table-valued parameters to update multiple field values in thousands of files.Before I used the user-defined table type, the stored procedure I relied on involved a cursor loop that took too long to execute, caused numerous deadlocks, and had a negative effect on database performance.With SQL Server 2008’s new user-defined table type, you no longer have to spend hours trying to figure out ways to update thousands of records.You can also add primary key and unique constraints to the table type.The ddl_in Listing 1 sets up the test database Movie Rental DB with a table and the table type. Listing 1: Code to Populate Movie Rental DB with a Table and Table Type CREATE DATABASE \[Movie Rental DB\] USE Movie Rental DB CREATE TABLE Trans Log( Trans ID int IDENTITY(101,1) PRIMARY KEY not null, User ID int not null, Trans Date date not null, Type ID int not null) CREATE TYPE dbo.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 .