在表中插入数据时获取表的行数

沙漠珍足

这是示例数据和模式
0 I2 S# L: d; g' O: q3 wCREATE TABLE [dbo].[Customer]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](500) NOT NULL, [IsDelete] [bit] NOT NULL,
3 X6 H/ M# z4 vCONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
5 @  `2 N4 D& D8 y( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Invoice]([Id] [int] IDENTITY(1,1) NOT NULL,[CustomerId] [int] NOT NULL, [Amount] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED
2 _2 J: V2 s: {4 F- v( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
( e2 T, W" O. n% I0 G" {Insert Into Customer (Name)
* U0 D7 ?0 n* x# y* Q4 [  d0 JValues
('Jhon'),('-Jack'),('Mary'),('-Sam');
Insert into Invoice (CustomerId,Amount)
Values (1, 22.00),(1, 34.50),(2,20.67),(2,34.67),(3,45.89),(3,20.00),(4,30.00),(4,20.00)
ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_IsDelete]  DEFAULT ((0)) FOR [IsDelete]
0 t" ?' l; I1 x7 O% B: }" n; r; ?GO
ALTER TABLE [dbo].[Invoice]  WITH CHECK ADD  CONSTRAINT [FK_Invoice_Invoice] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[Invoice] CHECK CONSTRAINT [FK_Invoice_Invoice]
$ A" b& x9 a' }1 S9 X; RGO
" ~2 R  I* W* D- [我有以下SQL查询
DECLARE @invoiceTable TABLE (
Id INT NOT NULL,
) m8 y$ _; D1 A+ u6 L1 v2 g4 [CustName NVARCHAR(MAX) NOT NULL,
FilteredRecords INT DEFAULT 0 NOT NULL
: k2 {- c8 V6 o0 H3 Y)
Insert into @invoiceTable(
Id,
CustName,
# E3 v  d: m  ]FilteredRecords -- ****Here I need help****
)
Select
I.Id,
R.Name,
(Select Count(Id) from @invoiceTable)
from Invoice I Inner Join Customer R on I.CustomerId = R.Id
UPDATE @invoiceTable SET CustName= 'Anonymous' WHERE CustName Like'%-%'
. E8 b+ B! ^  \  y8 t# RSelect * from @invoiceTable
4 p9 o+ ~1 c0 O& bwhere Id In (1,4)
: X. M1 {" X2 z+ F0 d! @现在我正在关注明显的结果。
: |- L( B3 @) U+ nId       CustName   FilteredCount  
- `9 _% X. u! U- U4 L7 T$ e-------- ---------- ----------------
. r4 i  M+ W7 F( d! h1        John       0
4        Anonymous  0
但是相反，我需要FilteredCount列中@invoiceTable的总行数。
预期结果是
/ i3 Q% H  o0 L/ EId       CustName   FilteredCount  
$ @% r( k, R1 H- C' ]7 y! }9 ~-------- ---------- ----------------
# }) u( S& @' J7 m- x: Y1        John       2
4        Anonymous  2
因为我在@invoiceTable中只有两行，所以我希望2在FilteredCount列中
有什么方法可以实现这一目标，请帮帮我。
               
解决方案: