Inserting two rows that are almost identical to each other

Question

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?

Answer 1

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.

Answer 2

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