Prev: recurring balance?
Next: Function or insert/into temp table
From: Andy B. on 22 Mar 2010 13:12
I know how to insert into a table using table parameters, but how do you
delete/update with them?


From: Plamen Ratchev on 22 Mar 2010 13:24
The same way you would use a regular table to perform delete/update:

MERGE INTO TargetTable AS T
USING @TableValuedParameter AS S
ON T.keycol = S.keycol
WHEN MATCHED
THEN UPDATE SET datacol = S.datacol;

DELETE TargetTable
WHERE EXISTS(SELECT *
FROM @TableValuedParameter AS T
WHERE T.keycol = TargetTable.keycol);

UPDATE TargetTable
SET datacol = (SELECT T.datacol
FROM @TableValuedParameter AS T
WHERE T.keycol = TargetTable.keycol)
WHERE EXISTS(SELECT *
FROM @TableValuedParameter AS T
WHERE T.keycol = TargetTable.keycol);

--
Plamen Ratchev
http://www.SQLStudio.com
From: Tom on 22 Mar 2010 14:17
On Mar 22, 1:12 pm, "Andy B." <a_bo...(a)sbcglobal.net> wrote:
> I know how to insert into a table using table parameters, but how do you
> delete/update with them?

The input table defines the changes that need to be made. Use an SQL
MERGE statement or traditional INSERT, UPDATE and DELETE statements
that have a join with the input table parameter. For me to get more
specific I would need the specific example of what you want to do.
From: Andy B. on 23 Mar 2010 14:12

"Tom" <tom.groszko(a)charter.net> wrote in message
news:2a05ea91-8d2c-4769-ac83-acf38ad5622c(a)l25g2000yqd.googlegroups.com...
On Mar 22, 1:12 pm, "Andy B." <a_bo...(a)sbcglobal.net> wrote:
> I know how to insert into a table using table parameters, but how do you
> delete/update with them?

The input table defines the changes that need to be made. Use an SQL
MERGE statement or traditional INSERT, UPDATE and DELETE statements
that have a join with the input table parameter. For me to get more
specific I would need the specific example of what you want to do.

I have the table parameter as below:

create type HeadlineTable as table
(HeadlineTitle nvarchar(200) not null,
HeadlineDescription nvarchar(500) not null,
HeadlineContent nvarchar(max) not null,
HeadlineStartDate date not null,
HeadlineEndDate date not null,
unique key(HeadlineTitle))

I have a table the closely resembles the HeadlineTable type below:

create table Headlines
(HeadlineID int identity(1,1) not null,
HeadlineTitle nvarchar(200) not null,
HeadlineDescription nvarchar(500) not null,
HeadlineContent nvarchar(max) not null,
HeadlineStartDate date not null,
HeadlineEndDate date not null,
primary key(HeadlineID),
unique key(HeadlineTitle))

Using the HeadlineTable type, I have a stored procedure that allows for
inserting multiple rows into the table at once. What I need to do now, is to
be able to update the table and delete from the table using the
HeadlineTable type in the same way. To be able to update/delete multiple
rows at the same time.


 | 
Pages: 1
Prev: recurring balance?
Next: Function or insert/into temp table