22,207
社区成员
发帖
与我相关
我的任务
分享
--+++++++++++++++++++++++++++++
create table Customer_Info(
Cust_ID int identity(1,1),
Cust_Name varchar(50)
)
go
--+++++++++++++++++++++++++++++
create function [dbo].[fun_GetCustomID](
@Cust_Name varchar(50)
)
returns int
as
begin
declare @Cust_ID int
select @Cust_ID = Cust_ID from Customer_Info where Cust_Name=@Cust_Name
return(@Cust_ID)
end
go
--+++++++++++++++++++++++++++++
insert into Customer_Info(Cust_Name) values('Richard')
insert into Customer_Info(Cust_Name) values('Jerry')
insert into Customer_Info(Cust_Name) values('Kerry')
insert into Customer_Info(Cust_Name) values('Bel')
insert into Customer_Info(Cust_Name) values('Rason')
go
create table #Customer(
Cust_Name varchar(50)
)
insert into #Customer
output dbo.fun_GetCustomID(inserted.Cust_Name)
select Cust_Name from Customer_Info
/*
drop table Customer_Info
drop table #Customer
drop function fun_GetCustomID
*/
insert into #Customer
select dbo.fun_GetCustomID(Cust_Name) from Customer_Info
I. Using OUTPUT with an INSERT statement
The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar table variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. However, note that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the INSERTED.ScrapReasonID column.
USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO