SQL Server 2017 Nested Insert or using results of one insert in another insert

My problem can be summarized with the following contrived example.
I have several tables:

AUTOMOBILE
AutoId Make Model Color

BATCH
BatchId SaleDate

INVENTORY
InventoryId AutoId BatchId CustomerName(string) ParkingSlipId

PARKING
ParkingSlipId SlipStatus(String)


Currently, there are no rows in the INVENTORYtable or BATCH table. I would like to, with a query or stored procedure in SQL Server Management Studio, create some fake data in INVENTORY. To do this, I will simply take the first 10 rows of AUTOMOBILE table and use the AutoId from AUTOMOBILE as INVENTORY.AutoId. I also need to create a new row in BATCH for each entry in INVENTORY. I'll use the BATCH.BatchId as INVENTORY.BatchId. CustomerName can for now, just be the word "Fred". For the ParkingSlipId, I can take any row from PARKING where the SlipStatus is "Empty" and use that ParkingSlipId and change the status to "Full".

I've played around with this problem with limited success. I can for instance use something like:

INSERT [BATCH]
VALUES (GETDATE())
DECLARE @tempLoadBatchId bigint
SELECT @tempLoadBatchId = @@IDENTITY


I can the use tempLoadBatchId in my next insert (into INVENTORY)

INSERT [INVENTORY] (AutoId BatchId CustomerName ParkingSlipId)
VALUES (3, @tempLoadBatchId, "Fred",77);

UPDATE [PARKING] set SlipStatus= 'FullBoat' where SlipId = 77


Notice this is just for one row AND I simply hardcoded the AutoId.

I'd like to automate this. Using Cursors seems one way to do it, and that's certainly an option (these are tiny tables and the performance won't be an issue) but I'd also like to know how to do it without cursors.
Thanks, Dave

MORE BACKGROUND AND DESIRED RESULTS
In response to comments, I've added some more info. I can backup and attach the entire DB if that's of use. I assume StackOverflow will allow that.

Here is the current AUTO table

AutoId Make Model Colour
1 Ford Taurus White
2 GM Cadilac Silver
3 Toyota Prius Blue
4 Honda Pilot Green
5 Kia Sonata Yellow


Here is the current PARKING Table

ParkingSlipId SlipStatus
1 Empty

2 Empty

3 Empty

4 Empty

5 Empty

6 Empty

7 Empty

8 Empty

For each row in AUTO, I would like to create a new entry in BATCH, and then use that BatchId to create a new INVENTORY entry. In that new INVENTORY entry, I'd like to use the row from AUTO and assign a parkingId and change the corresponding parkingid in PARKING TO FULL. When all this is done, the tables BATCH, INVENTORY, and PARKING should look like:

PARKING
ParkingSlipId SlipStatus
1 Full
2 Full
3 Full
4 Full
5 Full
6 Empty
7 Empty
8 Empty

BATCH
BatchId SaleDate
1 2018-09-15 00:00:00.000
2 2018-08-20 00:00:00.000
3 2018-06-22 00:00:00.000
4 2018-09-12 00:00:00.000
5 2018-07-01 00:00:00.000


INVENTORY
InventoryId AutoId BatchId ParkingId CustomerName
1 1 1 1 Fred
2 2 2 2 Mary
3 3 3 3 Fred
4 4 4 4 Ted
5 5 5 5 James


Is there more info I can provide? Or in a different format? Many thanks!

output

insert

1 Answer
1

Here is one solution to the problem posed. However, I use "cursor", something I was trying to avoid. I'd be interested in set based operations as Xedni suggested in

/****** Script ******/

DECLARE @tempLoadBatchId bigint
DECLARE @tempAutoId int
DECLARE @tempParkingId int
DECLARE @counter bigint
SET @counter = 0

--- AUTOMOBILE STUFF
DECLARE cur CURSOR FOR
SELECT AutoId
FROM [CarExample].[dbo].[AUTOMOBILE]

OPEN cur
FETCH NEXT FROM cur INTO @tempAutoId

--- PARKING stuff
DECLARE curParking CURSOR FOR
SELECT ParkingSlipId
FROM [CarExample].[dbo].[PARKING] where SlipStatus = 'Empty'

OPEN curParking
FETCH NEXT FROM curParking INTO @tempParkingId
-----
WHILE @counter < 10
BEGIN
INSERT [CarExample].[dbo].[BATCH]
VALUES (GETDATE())
SELECT @tempLoadBatchId = @@IDENTITY

INSERT CarExample.[dbo].INVENTORY (AutoId, BatchId, ParkingId, CustomerName)
VALUES (@tempAutoId, @tempLoadBatchId, @tempParkingId, 'Sally');
SET @counter = @counter + 1

UPDATE [CarExample].[dbo].[PARKING] Set SlipStatus = 'Full' where ParkingSlipId = @tempParkingId
FETCH NEXT FROM cur INTO @tempAutoId
FETCH NEXT FROM curParking INTO @tempParkingId
END

CLOSE cur
DEALLOCATE cur
CLOSE curParking
DEALLOCATE curParking


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy