Is There A Way To Do Another Query Within The Insert Query?

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

Solution 1:

Setup:

USE TempDB;
GO

CREATETABLE dbo.Leads
(
    LeadID VARCHAR(64),
    CreateUser VARCHAR(32),
    CreateDate DATETIME NOTNULLDEFAULTCURRENT_TIMESTAMP,
    FirstName VARCHAR(32),
    AccountID INT
);

CREATETABLE dbo.Accounts
(
    AccountID INTIDENTITY(1,1),
    name VARCHAR(32) /* , ... other columns ... */
);

CREATETABLE dbo.Temp(name VARCHAR(32));

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

Query:

INSERT dbo.Accounts
(
    name
)
OUTPUT
    'Q'+cast(floor(999997* RAND(convert(varbinary, newid()))) asvarchar(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
DROPTABLE dbo.Temp, dbo.Accounts, dbo.Leads;

Solution 2:

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

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

Solution 3:

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()))) asvarchar(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@accountIDintINSERTINTO Account (col1,col2,col...)
values ('foo','bar','baz')

select@accountID=SCOPE_IDENTITY()

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

Solution 4:

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

Solution 5:

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

CREATETABLE #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()))) asvarchar(20)) 
       ,'U6UJ9000S'
       ,CURRENT_TIMESTAMP
       ,t.name
       ,i.AccountID

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