INSERT Multiple Records INTO a SQL Table

[richard2007]

When a user adds a record to a table (Products) we need to create records in another related table.

I think i have the script to do this for a single entry but wondered if i just need to duplicate this for every record we need to add to the table or is there a way i can add multiple records to the same table at the same time in a more efficient way? The only field value which changes for each record is Doc_Name

'create records for documents which are required for this mortgage.  Need KFI, App, Research and RoS

	dim session.prodid as c
	session.prodid = e.rtc.lastPrimaryKeyValue
	
	' DIM standard variables
' -----------------------
	dim cn as sql::Connection
	dim rs as sql::ResultSet
	dim args as sql::Arguments

' Here's the Query:
' --------------------

	sql= <<%a%
	INSERT INTO Activities 
		(CaseID, ProdID, Provider, Doc_Path, Created, Created_By, Activity_Type, Doc_Name, Doc_Extn)
	values (:caseref, :ProdID, :Provider, :Doc_path, :Created, :Created_By, :Activity_Type, :Doc_Name, :Doc_Extn)
	%a%

	' set argument value. 
	args.set("CaseID", val(session.CaseID))
	args.set("ProdID", val(session.prodid))
	args.set("Provider", Provider)
	args.set("Doc_Path", Doc_Path)
	args.set("Created", Date())
	args.set("Created_By", session.contactname)
	args.set("Activity_Type", "Document")
	args.set("Doc_Name", "Key Facts Illustration")
	args.set("Doc_Extn", ".pdf")
	
	cn.open("::name::MM3")

	cn.execute(sql, args)' Execute the query - don't forget the args:
	cn.close()

[lvasic]

Re: INSERT Multiple Records INTO a SQL Table

Are the base records already saved in SQL or are they still on the client side waiting to be saved?

[peteconway]

Re: INSERT Multiple Records INTO a SQL Table

Start using triggers and stored procedures.

[richard2007]

Re: INSERT Multiple Records INTO a SQL Table

Are the base records already saved in SQL or are they still on the client side waiting to be saved?

the child records need to be added to the table when the parent record (product) is saved to the table, so in the after validate event of the add product UX

[richard2007]

Re: INSERT Multiple Records INTO a SQL Table

Start using triggers and stored procedures.

i understand this would be better but i have no experience of stored procedures and wouldn't know how to write one.

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

you use triggers, after insert is probably the way to go.
if you post an example of the parent table and the related child table one of us can show the initial steps.

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

here is an example for you to see and adjust:
https://www.screencast.com/t/B54lfM59unXC

[richard2007]

Re: INSERT Multiple Records INTO a SQL Table

So i have delved in to creating a SP and so far i have created a SP using Navicat, (ive just seen Ghandi's post with an example so will have a look at that).

ALTER PROCEDURE [dbo].[ReqMortDocs]
	
  @CaseID AS int =0 ,
  @ProdID AS int =0 ,
  @Org_Name AS nvarchar ,
  @Doc_Path AS nvarchar ,
  @Created_By AS nvarchar
AS
BEGIN
  INSERT INTO dbo.activities
		(caseid, prodid, org_name, doc_path, Created_by)

	VALUES 
		(@CaseID, @ProdID, @Org_Name, @Doc_Path, @Created_By )

and then i have called it from the 'After Validate' event on the UX, i have harvested this from different posts on the msg board. I know the script runs as an action after this script is executing ok, but the SP isn't being executed.

DIM cn as SQL::Connection
	dim flagResult as l
	flagResult = cn.open("::Name::MM3")
	dim args as sql::arguments
	dim sql as c

	sql = <<%sql%
	Exec ReqMortDocs(:CaseID, :ProdID, :Org_Name, :Doc_path, :ErrorMsg)
	%sql%
	
	args.set("CaseID", 2916)
	args.set("ProdID", 0001) 
	args.set("Provider", "DMC")
	args.set("Doc_Path", "Cdrive")
	args.set("ReturnVal",-1,sql::ArgumentUsage::InputOutputArgument)
	args.set("ErrorMsg","unknown",sql::ArgumentUsage::InputOutputArgument)
	
	flagResult = cn.Execute(sql,args)
	cn.close()

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

follow up to show you that you don't need to call the trigger as you need to in SP ( this is in mYSQl, same for you in MSSQL)
https://www.screencast.com/t/J7wZak83

[richard2007]

Re: INSERT Multiple Records INTO a SQL Table

follow up to show you that you don't need to call the trigger as you need to in SP ( this is in mYSQl, same for you in MSSQL)
https://www.screencast.com/t/J7wZak83

Thanks Ghandi, i now understand how the trigger works, which actually seems a lot easier. I am using what looks like a different version of Navicat to you and am having a little trouble creating the Trigger. When saving the trigger i get a message 'Must declare the scalar variable "@Doc_Path"

This is the SQL Preview of the Trigger - i need to ensure this trigger only runs if a condition is met - products table > GRP = "Mortgage"

CREATE TRIGGER [dbo].[NewTrigger]
ON [dbo].[products]
AFTER INSERT
AS
BEGIN

	SET @Doc_Path = 'KFI', @CaseID = 2916, @ProdID = 1001, @Org_Name = 'DMC', @Created_By = 'RCHarris'
	INSERT INTO dbo.activities (caseid, prodid, org_name, doc_path, Created_by) VALUES (@CaseID, @ProdID, @Org_Name, @Doc_Path, @Created_By )
END
GO

PS, i really appreciate you taking time out to make these videos, they are very helpful.

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

see here with if condition
https://www.screencast.com/t/zESvE0DID

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

i am running out, will come back later to see if you solved it.

[richard2007]

Re: INSERT Multiple Records INTO a SQL Table

Ok, so i have created the trigger as Ghandi has done, the trigger saves ok. I created the Trigger on the Products Table with the aim of inserting into the activities table.

I can add a record to the products table but the tigger is not inserting in to the activities table.

Trigger on Products.PNG

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

okay see below:
https://www.screencast.com/t/El1H1qVkt

BEGIN
declare @application_id int;
declare @customer_name varchar(30);
declare @document_needed varchar(30);
declare @job varchar(30)
SELECT @job = job FROM INSERTED;
IF (@job = 'Mortgage') 
BEGIN
SELECT @application_id = i.id from inserted i;
SELECT @customer_name = i.name from inserted i;

SET @document_needed = 'HHS';
INSERT INTO document (application_id, customer, document_needed) VALUES ( @application_id, @customer_name, @document_needed);

SET @document_needed = 'SOCIAL SECURITY';
INSERT INTO document (application_id, customer, document_needed) VALUES ( @application_id, @customer_name, @document_needed);

SET @document_needed = 'DRIVERS LICENSE';
INSERT INTO document (application_id, customer, document_needed) VALUES ( @application_id, @customer_name, @document_needed);
END 
END

just to show it works with alpha dialog
https://www.screencast.com/t/ukPOE3ZE4j

[GGandhi]

Re: INSERT Multiple Records INTO a SQL Table

Ok, so i have created the trigger as Ghandi has done, the trigger saves ok. I created the Trigger on the Products Table with the aim of inserting into the activities table.

I can add a record to the products table but the tigger is not inserting in to the activities table.

[ATTACH=CONFIG]43497[/ATTACH]

may be because you have some errors in the trigger.
you are missing some semicolons, and you did not declare @Caseid before using it ( i know you did not use it in the insert statement)