Inserting new BusinessEntityID in AdventureWorks2017

Question

How do you insert new BusinessEntityID's into Person.Person for multiple records? There are foreign key constraints on Person.BusinessEntity. I can create a new Person.BusinessEntity and output using:

IF OBJECT_ID('tempdb..#bii') IS NOT NULL
DROP TABLE dbo.#bii
GO
CREATE TABLE dbo.#bii
(
BusinessEntityID int,
rowguid varchar(200) NOT NULL,
ModifiedDate datetime NOT NULL
)
GO

INSERT INTO [Person].[BusinessEntity]
(rowguid)

OUTPUT inserted.* INTO dbo.#bii

VALUES (default)

But not sure how to do this with multiple records from a staging table. Thx

j mcqueen · Accepted Answer

So I understood the concept of constraints, but I think I confused myself over-interpreting the default values properties in the BusinessEntityID, rowguid, and ModifiedDate. Here is the solution I created from some modified code I found using OUTPUT:

DECLARE @Person TABLE (
[PersonType] [nchar](2) NOT NULL,
[NameStyle] VARCHAR(200) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] VARCHAR(200) NOT NULL,
[MiddleName] VARCHAR(200) NULL,
[LastName] VARCHAR(200) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NOT NULL,
[rowguid] VARCHAR(200) NOT NULL
)

INSERT INTO @Person
SELECT TOP 10
[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,newid()
FROM [Person].[Person]
;

DECLARE @InsertedRows TABLE (
BusinessEntityID int,
rowguid varchar(200) NOT NULL,
ModifiedDate datetime NOT NULL
)

INSERT INTO [Person].[BusinessEntity]
(rowguid)
OUTPUT inserted.*
INTO @InsertedRows
SELECT top 10
rowguid
FROM
@Person AS I
WHERE
NOT EXISTS (SELECT 1 FROM person.person
WHERE BusinessEntityID = I.BusinessEntityID)

INSERT INTO [Person].[Person]
(BusinessEntityID
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion])
SELECT
BusinessEntityID
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]

FROM
@Person AS P
JOIN @InsertedRows I ON I.rowguid = P.rowguid
WHERE
NOT EXISTS (SELECT 1 FROM person.person
WHERE BusinessEntityID = I.BusinessEntityID)

Forum Discussion

Inserting new BusinessEntityID in AdventureWorks2017

1 Reply

Resources