在某些字段中插入用户定义表中的数据，其他字段必须使用标量变量

How would I make use of my user-defined table that comes loaded with data, insert into a table but 1 of the table fields won't be using the data from the User Defined Table. I want a scalar variable to take its place.

如何使用加载了数据的用户定义表,插入表中,但其中一个表字段不会使用用户定义表中的数据。我想要一个标量变量来代替它。

@dataTableType is the following user-defined table:

@dataTableType是以下用户定义的表:

CREATE TYPE [dbo].[NewSequenceType] AS TABLE (
    [FK_Sequence] [int] NULL,
    [FK_Status] [int] NULL,
    [FK_Shift] [int] NULL,
    [PK_SequenceType] [int] NULL,
    [TypeName] [varchar](30) NULL,
    [SequenceName] [varchar](100) NULL,
    [SequenceDetails] [varchar](400) NULL,
    [SequenceStatus] [varchar](15) NULL,
    [SequenceShift] [varchar](15) NULL,
    [EmployeeId] [varchar](8) NULL,
    [EquipmentId] [varchar](25) NULL,
    [Comments] [varchar](512) NULL,
    [EnteredDate] [datetime] NULL
)

My stored procedure:

我的存储过程:

ALTER PROCEDURE [dbo].[spNewInspectionEntry] (@dataTableType NewSequenceType READONLY)
    INSERT INTO dbo.PIT_Inspection (FK_Sequence, FK_Status, FK_Shift, FK_EmployeeName, FK_EquipmentName, Comments, EnteredDate)
        SELECT 
            FK_Sequence, FK_Status, FK_Shift, EmployeeId, 
            EquipmentId, Comments, EnteredDate
        FROM 
            @dataTableType;

What I'm looking to do:

我想做什么:

DECLARE @EmployeeIDExists varchar(8);
--Note that I only care about one row, this is Ok.
SELECT TOP 1 @EmployeeIDExists = EmployeeId FROM @dataTableType;

--Retrieve PKEmployeeId
SELECT @EmployeeIdPK = PK_EmployeeName
FROM dbo.PIT_EmployeeName
WHERE EmployeeId = @EmployeeIDExists

--Now I need to insert the value of @EmployeeIdPK? Along with the rest, it needs to replace EmployeeID and EquipmentId, Not sure how to...
INSERT INTO dbo.PIT_Inspection (FK_Sequence, FK_Status, FK_Shift, FK_EmployeeName, FK_EquipmentName, Comments, EnteredDate)
    SELECT 
        FK_Sequence, FK_Status, FK_Shift, EmployeeId, 
        EquipmentId, Comments, EnteredDate
    FROM 
        @dataTableType;

How can I get @EmployeeIdPK in Along with the rest, it needs to replace EmployeeID, I don't want the EmployeeId from the user-defined table to go there, I want the variable @EmployeeIdPK instead.

如何获得@EmployeeIdPK以及其他内容,它需要替换EmployeeID,我不希望用户定义的表中的EmployeeId去那里,我想要变量@EmployeeIdPK。

1 个解决方案

#1

You can put any expression, variable, and literal value in the SELECT list. Just keep in mind that those values will be repeated for all rows.

您可以在SELECT列表中放置任何表达式,变量和文字值。请记住,所有行都会重复这些值。

Meaning:

SELECT Field1,
       Field2,
       'hello' AS [LiteralText],
       5 AS [LiteralNumber],
       @Variable AS [ValueFromVariable]
FROM TableName;

will repeated those literal and variable values for all rows.

将为所有行重复这些文字和变量值。

Hence:

INSERT INTO dbo.PIT_Inspection (FK_Sequence, FK_Status, FK_Shift, FK_EmployeeName,
         FK_EquipmentName, Comments, EnteredDate)
    SELECT 
        FK_Sequence, FK_Status, FK_Shift, @EmployeeIdPK, 
        EquipmentId, Comments, EnteredDate
    FROM 
        @dataTableType;

#1