SQL Insert Creates multiple records instead of just one
-
Hi there, I have created an insert statement to create a new record in a database. This is used from a task automation program and the client record is mapped using that so records ARE created on the correct client but i only want 1 record to be created, at the moment if a client has 10 quotes then it will insert 10 new quotes instead of just 1. My code is below: CREATE PROCEDURE [dbo].[ietsp_CreateDODQuotes] @ContactID AS INT, @TaxYear AS INT AS SELECT @TaxYear = TaxProYear FROM FlightDeckTest.dbo.iet_TaxYears ty WHERE IsCurrent = 'T' /** Add PTM DOD Quote **/ BEGIN INSERT INTO FlightDeckTest.dbo.Lead ( ContactID, Owner, Status, EntitySubType, ietCreateDate, ietMumbaiStatus, ietTaxAdmin, ietTaxYear, ietQuoteIncl, ietQuoteExcl, ietVAT, ietProdServID, ietProdServDesc, ietPayMethod, ietCardType, ietCardHolder, ietCardNumber, ietAcctNo, ietSortCode, ietIssueNumber, ietSecurityCode, ietInstallments, ietMonthRepay, ietFinalWithIns, ietFinalInstallment, ietAcctName, ietBankName ) SELECT l.ContactID, l.Owner, 'AWAUTH', l.entitySubType, GETDATE(), 0, l.ietTaxAdmin, @TaxYear, l.ietQuoteIncl + CONVERT(DECIMAL(7,2),176.25), l.ietQuoteExcl, l.ietVAT, l.ietProdServID, 'PTM DOD - ie taxguard', 'INVTP', l.ietCardType, l.ietCardHolder, l.ietCardNumber, l.ietAcctNo, l.ietSortCode, l.ietIssueNumber, l.ietSecurityCode, l.ietInstallments, l.ietMonthRepay, l.ietFinalWithIns, l.ietFinalInstallment, l.ietAcctName, l.ietBankName FROM FlightdeckTest.dbo.Lead l INNER JOIN FlightDeckTest.dbo.Contact c ON l.ContactID = c.ContactID WHERE ((l.ietProdServDesc LIKE '%PTM%' OR l.ietProdServDesc LIKE '%Tax Return%')) AND (l.Status = 'ACT') AND (l.ietTaxYear IN (SELECT TaxProYear FROM iet_TaxYears WHERE IsCurrent = 'T')) END GO Any help would be greatly appreciated.
-
Hi there, I have created an insert statement to create a new record in a database. This is used from a task automation program and the client record is mapped using that so records ARE created on the correct client but i only want 1 record to be created, at the moment if a client has 10 quotes then it will insert 10 new quotes instead of just 1. My code is below: CREATE PROCEDURE [dbo].[ietsp_CreateDODQuotes] @ContactID AS INT, @TaxYear AS INT AS SELECT @TaxYear = TaxProYear FROM FlightDeckTest.dbo.iet_TaxYears ty WHERE IsCurrent = 'T' /** Add PTM DOD Quote **/ BEGIN INSERT INTO FlightDeckTest.dbo.Lead ( ContactID, Owner, Status, EntitySubType, ietCreateDate, ietMumbaiStatus, ietTaxAdmin, ietTaxYear, ietQuoteIncl, ietQuoteExcl, ietVAT, ietProdServID, ietProdServDesc, ietPayMethod, ietCardType, ietCardHolder, ietCardNumber, ietAcctNo, ietSortCode, ietIssueNumber, ietSecurityCode, ietInstallments, ietMonthRepay, ietFinalWithIns, ietFinalInstallment, ietAcctName, ietBankName ) SELECT l.ContactID, l.Owner, 'AWAUTH', l.entitySubType, GETDATE(), 0, l.ietTaxAdmin, @TaxYear, l.ietQuoteIncl + CONVERT(DECIMAL(7,2),176.25), l.ietQuoteExcl, l.ietVAT, l.ietProdServID, 'PTM DOD - ie taxguard', 'INVTP', l.ietCardType, l.ietCardHolder, l.ietCardNumber, l.ietAcctNo, l.ietSortCode, l.ietIssueNumber, l.ietSecurityCode, l.ietInstallments, l.ietMonthRepay, l.ietFinalWithIns, l.ietFinalInstallment, l.ietAcctName, l.ietBankName FROM FlightdeckTest.dbo.Lead l INNER JOIN FlightDeckTest.dbo.Contact c ON l.ContactID = c.ContactID WHERE ((l.ietProdServDesc LIKE '%PTM%' OR l.ietProdServDesc LIKE '%Tax Return%')) AND (l.Status = 'ACT') AND (l.ietTaxYear IN (SELECT TaxProYear FROM iet_TaxYears WHERE IsCurrent = 'T')) END GO Any help would be greatly appreciated.
your not summing anything to only make one record. It will insert all the records that match your where clause so if it matches more then one will insert more then one record. and please put your code into a code block it makes it easier to read!