从最后插入的行获取数据

发布于 2024-12-21 07:53:38 字数 535 浏览 1 评论 0原文

我试图弄清楚如何从我刚刚使用的 INSERT 语句中获取名字和姓氏。它创建了该行，我想在下一个插入语句中使用名字和姓氏，我将如何执行此操作？

Declare @idnt int

INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) VALUES ('Richard','Burns',NULL);

SELECT @idnt = SCOPE_IDENTITY()

INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner)
VALUES (@idnt, '12/13/2011 10:55AM', 'Contact ' + @fname + " " + @lname + ", was automatically added by the system', 'Contact was automatically added by this system', 'System')

原文

I am trying to figure out how I would get the firstname and lastname from the INSERT statement that I just used. It created the row, and I want to use the First Name and the Last Name to use in the next insert statement, How would I go about doing this?

Declare @idnt int

INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) VALUES ('Richard','Burns',NULL);

SELECT @idnt = SCOPE_IDENTITY()

INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner)
VALUES (@idnt, '12/13/2011 10:55AM', 'Contact ' + @fname + " " + @lname + ", was automatically added by the system', 'Contact was automatically added by this system', 'System')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

转瞬即逝 2024-12-28 07:53:38

使用输出！

INSERT INTO dbo.Activity (
    ContactID, 
    ActivityDate, 
    ActivityName, 
    ActivityNote, 
    ActivityOwner
)
SELECT 
    ContactID, 
    GETDATE(), 
    'Contact ' + ContactFirstName + ' ' + ContactLastName, 
    'Contact was added by the system.', 
    'System'
FROM (
    INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) 
    OUTPUT inserted.ContactID, inserted.ContactFirstName, inserted.ContactLastName
    VALUES ('Richard','Burns',NULL)
) x

有关输出魔力的更多信息，请参阅此处< /a>.

您还可以在表上使用触发器使其自动插入，而不必每次都手动插入。它还使用 inserted/deleted 表。

您可以这样做：

CREATE TRIGGER Contact_LogActivity ON dbo.Contacts FOR INSERT AS
INSERT INTO dbo.Activity (
    ContactID, 
    ActivityDate, 
    ActivityName, 
    ActivityNote, 
    ActivityOwner
)
SELECT
    ContactID,
    GETDATE(), 
    'Contact ' + ContactFirstName + ' ' + ContactLastName, 
    'Contact was added by the system.', 
    'System'
FROM
    Inserted

现在，每当将任何行插入到“联系人”中时，它都会将其记录到“活动”中。有关触发器的详细信息，请参阅此处。

Use output!

INSERT INTO dbo.Activity (
    ContactID, 
    ActivityDate, 
    ActivityName, 
    ActivityNote, 
    ActivityOwner
)
SELECT 
    ContactID, 
    GETDATE(), 
    'Contact ' + ContactFirstName + ' ' + ContactLastName, 
    'Contact was added by the system.', 
    'System'
FROM (
    INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) 
    OUTPUT inserted.ContactID, inserted.ContactFirstName, inserted.ContactLastName
    VALUES ('Richard','Burns',NULL)
) x

For more on the magic that is output, see here.

You can also use a trigger on the table to make it automagic rather than having to insert it manually each time. It uses the inserted/deleted tables, as well.

You would do that with this:

CREATE TRIGGER Contact_LogActivity ON dbo.Contacts FOR INSERT AS
INSERT INTO dbo.Activity (
    ContactID, 
    ActivityDate, 
    ActivityName, 
    ActivityNote, 
    ActivityOwner
)
SELECT
    ContactID,
    GETDATE(), 
    'Contact ' + ContactFirstName + ' ' + ContactLastName, 
    'Contact was added by the system.', 
    'System'
FROM
    Inserted

Now, whenever any row gets inserted into Contacts, it logs it into Activity. For more on triggers, see here.

回复收藏 0 原文

日裸衫吸 2024-12-28 07:53:38

使用 SCOPE_IDENTITY() 捕获的身份，查询表并将 ContactFirstName 和 ContactLastName 获取到您声明的变量中：@fname 和 @lname 分别：

...
SELECT @idnt = SCOPE_IDENTITY()

SELECT
    @fname = ContactFirstName
  , @lname = ContactLastName
FROM dbo.Contacts 
WHERE YOUR_IDENTITY_COLUMN_HERE = @idnt

INSERT INTO dbo.Activity ...

将 YOUR_IDENTITY_COLUMN_HERE 替换为您假设从中获取的标识列的名称SCOPE_IDENTITY() 来自并测试..

Using the identity caught by SCOPE_IDENTITY(), query back the table and grab the ContactFirstName and ContactLastName into your declared variables: @fname and @lname respectively:

...
SELECT @idnt = SCOPE_IDENTITY()

SELECT
    @fname = ContactFirstName
  , @lname = ContactLastName
FROM dbo.Contacts 
WHERE YOUR_IDENTITY_COLUMN_HERE = @idnt

INSERT INTO dbo.Activity ...

Replace YOUR_IDENTITY_COLUMN_HERE with the name of the identity column from which you assumed to grab SCOPE_IDENTITY() from and test..

回复收藏 0 原文

-小熊_ 2024-12-28 07:53:38

我认为您正在寻找带有 select 语句的插入。试试这个：

    INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner)
    SELECT @idnt, '12/13/2011 10:55AM', 'Contact ' + ContactFirstName + ' ' + ContactLastName + ', was automatically added by the system', 'Contact was automatically added by this system', 'System'
    FROM Contacts
    WHERE ContactId = @idnt

I think you are looking for an insert with a select statement. Try this:

    INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner)
    SELECT @idnt, '12/13/2011 10:55AM', 'Contact ' + ContactFirstName + ' ' + ContactLastName + ', was automatically added by the system', 'Contact was automatically added by this system', 'System'
    FROM Contacts
    WHERE ContactId = @idnt

回复收藏 0 原文

~没有更多了~