有没有办法在插入查询中执行另一个查询? [英] Is there a way to do another query within the insert query?

问题描述

好吧，这就是我要查询的...我刚刚添加了ACCOUNTID和@accountID部分，这显然不起作用

Ok so this is the query I have...I just added the ACCOUNTID and the @accountID portion which is obviosly not working

INSERT INTO Leads (
    LEADID,
    CREATEUSER,
    CREATEDATE,
    FIRSTNAME,
    MODIFYDATE,
        ACCOUNTID
) 
SELECT 
       'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20)) 
       ,'U6UJ9000S'
       ,CURRENT_TIMESTAMP
       ,'U6UJ9000S'    
       ,name
       ,@accountID

  FROM Temp

我想做的是先在帐户表中插入一个，并获取该ID，然后将插入ID添加到该表中，并将其添加到Leads表中.甚至有可能

What I am trying to do is do an insert into the account table first and get that id and add the insert id to this insert into the leads table. Is that even possible

基本上，对于Temp表中的每条记录，我都需要在account表中插入一条没有任何值的记录，只需要account_id，所以当我在Leads表中插入时，我具有要进行插入的帐户ID

SO basically for each record in the Temp table i need to insert a record in the account table with no values just need the account_id so when i insert in the leads table i have the account id to make that insert

推荐答案

设置:

USE TempDB;
GO

CREATE TABLE dbo.Leads
(
    LeadID VARCHAR(64),
    CreateUser VARCHAR(32),
    CreateDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FirstName VARCHAR(32),
    AccountID INT
);

CREATE TABLE dbo.Accounts
(
    AccountID INT IDENTITY(1,1),
    name VARCHAR(32) /* , ... other columns ... */
);

CREATE TABLE dbo.Temp(name VARCHAR(32));

INSERT dbo.Temp SELECT 'foo'
UNION SELECT 'bar';

查询:

INSERT dbo.Accounts
(
    name
)
OUTPUT
    'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20)),
    'U6UJ9000S',
    CURRENT_TIMESTAMP,
    inserted.name,
    inserted.AccountID
INTO dbo.Leads
SELECT name
FROM dbo.Temp;

检查:

SELECT * FROM dbo.Accounts;
SELECT * FROM dbo.Leads;

清理:

USE tempdb;
GO
DROP TABLE dbo.Temp, dbo.Accounts, dbo.Leads;

这篇关于有没有办法在插入查询中执行另一个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！