Is there a way to do another query within the inse

Question

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

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

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

Answer 1

set a variable to Scope_identity() (which returns the last id that was created) and use that

Answer 2

Setup:

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';

Query:

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;

Check:

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

Cleanup:

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

Answer 3

The problem you will probably end up hitting in practice with Aaron's use of composable DML is that chances are in reality you will have an FK defined to constrain Leads(AccountId) to a valid value in which case you will hit the error.

The target table 'dbo.Leads' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_foo'.

To avoid this issue you can use

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

Answer 4

It is not working because you are inserting 6 values but you are specifying only 5 columns:

These are 5 columns:

LEADID,
    CREATEUSER,
    CREATEDATE,
    FIRSTNAME,
        ACCOUNTID

Ant these are 6 values:

'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20)) 
       ,'U6UJ9000S'
       ,CURRENT_TIMESTAMP
       ,'U6UJ9000S'    
       ,name
       ,@accountID

I don't know where you get the @accountID from, but I imagine you define it somewhere else above.

You can get @accountID as follows, after you do the insert to the Account table:

select @accountID=scope_identity()

And then execute the insert into the Leads table.

UPDATE: EXAMPLE:

declare @accountID int 
INSERT INTO Account (col1,col2,col...)
values ('foo','bar','baz')

select @accountID=SCOPE_IDENTITY()

INSERT INTO Leads (
    LEADID,
    CREATEUSER,
    CREATEDATE,
    FIRSTNAME,
        ACCOUNTID
) 
values 
(
      'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20))  --leadid
       ,'U6UJ9000S' --createuser
       ,CURRENT_TIMESTAMP  --createdate
       ,t.name --firstname
       ,@accountID --accountID
)

Answer 5

You can declare an variable, set it to the desired id and the use the variable in the insert.

Answer 6

With SQL Server 2005 or higher you can use the OUTPUT clause.

CREATE TABLE #Inserted (AccountID, AccountName)

INSERT Account (AccountName)
OUTPUT Inserted.AccountID, Inserted.AccountName
INTO #Inserted

SELECT AccountName
FROM Temp

INSERT Leads (
    LEADID,
    CREATEUSER,
    CREATEDATE,
    FIRSTNAME,
        ACCOUNTID
) 
SELECT 
       'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20)) 
       ,'U6UJ9000S'
       ,CURRENT_TIMESTAMP
       ,t.name
       ,i.AccountID

  FROM Temp AS t
  JOIN #Inserted AS i ON t.AccountName= i.AccountName