trjhtr

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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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 tuition_amount FROM [dbo].[Grade] WHERE grade_id = 
 (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)
select "Tuition", Grade.tuition_amount, invoice.invoice_id
from Student
join invoice on student.student_id = invoice.student_id
join grade on student.grade_id = grade.grade_id
where student.invoice_date = @invoiceDtm
--if you really want to specifiy the first names
and student.first_name in ('Murdoch', 'Hartwell')