# Inserting two rows that are almost identical to each other

I'm trying to make test data for an application I'm writing, and some of my test data is very repetitive, involving inserting a row and then inserting another row that is identical except for one column. As such, I have very repetitive INSERT statements that make the file longer than I think it needs to be.

INSERT INTO [dbo].[InvoiceItem] (invoice_item_name, amount, invoice_id) VALUES
('TUITION',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = 'Murdoch')),
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = 'Murdoch' AND invoice_date = '2019-09-05'))),
('TUITION_SERVICE_FEE',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = 'Murdoch')) * 0.03,
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = 'Murdoch' AND invoice_date = '2019-09-05'))),
('TUITION',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = 'Hartwell')),
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = 'Hartwell' AND invoice_date = '2019-09-05'))),
('TUITION_SERVICE_FEE',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = 'Hartwell')) * 0.03,
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = 'Hartwell' AND invoice_date = '2019-09-05'))),


Is there a way to clean this up by any chance?

You should create a Stored Procedure that does this with a parameter called @firstname

it would come out way cleaner.

here is what the create would look like, I added in the @InvoiceDate parameters as well thinking that it was also something that was not static in the insert statement.

CREATE PROCEDURE Sproc_Name
@FirstName nvarchar(50)
,@InvoiceDate DateTime
AS
INSERT INTO [dbo].[InvoiceItem] (invoice_item_name, amount, invoice_id) VALUES
('TUITION',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = @FirstName)),
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = @FirstName AND invoice_date = @InvoiceDate))),
('TUITION_SERVICE_FEE',
(SELECT grade_id FROM [dbo].[Student] WHERE first_name = @FirstName)) * 0.03,
(SELECT invoice_id FROM [dbo].[Invoice] WHERE student_id =
(SELECT student_id FROM [dbo].[Student] WHERE first_name = @FirstName AND invoice_date = @InvoiceDate)))
GO


and then you would call it like this

EXECUTE Sproc_Name @FirstName = "Murdoch", @InvoiceDate = CONVERT(date, GETDATE())
-- CONVERT(date, GETDATE()) gives you 2018-04-05


I just used GetDate() for illustrative purposes, you can put in a string like "2018-04-05" and it should work as well.

Are you trying to insert all of the invoice items for all students with an invoice date of '2019-09-05'? If you use a single query to get all the students you are interested in creating invoice items for, then in the end you would have a total of two insert statements. The one below, then a similar one for the 'Tuition_Service_Fee'. Also, using table joins instead of sub queries is preferred in my opinion - more readable and can be better performing. But definitely, use variables instead of hardcoded values will allow the code to be reused whether you just call the script or create a stored procedure.

declare @invoiceDtm datetime

INSERT INTO [dbo].[InvoiceItem] (invoice_item_name, amount, invoice_id)