Create a Stored Procedure in SQL that has if and select statements that updates a table

I have four tables as follows in the attached image

Four Tables
The shipment table gets imported from another database daily.

I need a stored procedure/trigger that when records get added to the shipment table daily that it must check to see if there is an Asset.
If there is an asset then add the following record in the AssetHistory table which copies Asset ID, [Store #], and [O Tracking] into AssetHistory table.  If there is no asset then do nothing. How do I go about accomplishing this task?
JDay2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
My suggestion is that the entire process be handled with an SSIS package including the Shipment table load.

Assuming that is not something you're willing to tackle then can you define how you "check to see if there is an Asset"?
JDay2Author Commented:
I have a SSIS Package that runs to get the daily shipments which basically imports the new records into the shipment table.  I am not that well versed in creating stored procedures or how to accomplish.

The "check to see if there is an asset" would need to be something like this

Step 1\\\Get Frus ID and Serial Number to Check

select FRUS.FrusID, FRUS.PartNumber, Shipment.[Serial #], FRUS.CustomerID
from FRUS INNER JOIN Shipment ON Frus.PartNumber = Shipment.[Part #]

Step 2 \\\Lookup to see if data is in Asset table and get AssetID

select Asset.AssetID, Asset.FrusID, Asset.SerialNumber, Asset.CustomerID
from Asset
where Information from step 1if yes then update AssetHistory table  with information from shipment report such as [o tracking] and [store #]  if no record found do nothing.

I hope I explained it clearly.
Brian CroweDatabase AdministratorCommented:
Add a Multicast transform and send one output to insert the Shipment
Send the second output to a new Lookup Transform on Frus (Step 1)
Send matching output to a second Lookup Transform on Asset (Step 2)
Send matching output to an OLEDB destination to insert the AssetHistory table entries

Hope this makes sense
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

JDay2Author Commented:
Let me step back a minute and look at it if all the data is in mssql.  Can this be done without using SSIS and by creating procedures/triggers to accomplish?
Brian CroweDatabase AdministratorCommented:
Yes you could create a trigger or a scheduled job to handle similar logic.
JDay2Author Commented:
How do I write a trigger/stored procedure to accomplish instead of ssis?
Brian CroweDatabase AdministratorCommented:
Can I get you to script out the schema creation and data insert for the pertinent tables.  You can do this through SSMS (Right-click on database -> Tasks -> Generate Scripts and don't forget to change the options to include data as well)  I certainly don't need all of the data so if you just want to pare it down to a half dozen rows or something that would be fine.

This will allow me to write a working example instead of air code.  Please include examples of each use case (Frus exists / Frus does not exist)  Is it possible that the Asset wouldn't exist?
JDay2Author Commented:
Attached are the files requested.  If you need anymore information, please let me know.

Thanks.
script.sql
test.sql
Brian CroweDatabase AdministratorCommented:
JDay,

I have gotten a little busy lately...I will try to get you something tomorrow.
Brian CroweDatabase AdministratorCommented:
I'm running into NOT NULL requirements on AssetHistory.  What do you want in

        [CreatedBy] [nchar](50) NOT NULL,
      [CreatedOn] [datetime] NOT NULL,
      [CreateId] [nvarchar](50) NOT NULL,
      [ModifiedBy] [nvarchar](50) NOT NULL,
      [ModifiedById] [uniqueidentifier] NOT NULL,
      [ModifiedOn] [datetime] NOT NULL,
Brian CroweDatabase AdministratorCommented:
This is what I have so far.  I would recommend calling the stored procedure within your SSIS package upon completion but you could also schedule it.  I try to avoid triggers whenever possible.

CREATE PROCEDURE UpdateAssetHistoryFromShipmentReport
AS

INSERT INTO AssetHistory (AssetID, AssetMoveID, StoreID, TerminalID, Tracking, Carrier, CustomerID,
	CreatedBy, CreatedOn, CreateId, ModifiedBy, ModifiedById, ModifiedOn)
OUTPUT INSERTED.*
SELECT A.AssetID,
	3 AS AssetMoveID,
	R.[Store #] AS StoreID,
	25 AS TerminalID,
	R.[Out Tracking Number] AS Tracking,
	'UPS' AS Carrier,
	A.CustomerID,
	'test' AS CreatedBy,
	GETDATE() AS CreatedOn,
	'test' AS CreatedId,
	'test' AS ModifiedBy,
	NEWID() AS ModifiedById,
	GETDATE() AS ModifiedOn
FROM Frus AS F
INNER JOIN HotSwapOnsiteShipmentReport AS R
	ON F.PartNumber = R.[Part #]
INNER JOIN Asset AS A
	ON F.CustomerID = A.CustomerID
	AND F.FrusID = A.FrusID
	AND R.[Serial #] = A.SerialNumber
LEFT OUTER JOIN AssetHistory AS AH
	ON AH.AssetID = 1
	AND AH.AssetMoveID = 3
	AND AH.StoreID = R.[Store #]
	AND AH.TerminalID = 25
	AND AH.Tracking = R.[Out Tracking Number]
	AND AH.Carrier = 'UPS'
	AND AH.CustomerID = A.CustomerID
WHERE AH.AssetHistoryID IS NULL

Open in new window

JDay2Author Commented:
[createdby] =admin
[createdon] =currentdatetime
[createid] =5095aff9-7192-4b3b-a8f1-df6401973fa2
[modifiedby]=admin
[modifiedbyid]=5095aff9-7192-4b3b-a8f1-df6401973fa2
[modifiedon]=currentdatetime
Brian CroweDatabase AdministratorCommented:
Just plug those into the placeholders I included...

ALTER PROCEDURE UpdateAssetHistoryFromShipmentReport
AS

INSERT INTO AssetHistory (AssetID, AssetMoveID, StoreID, TerminalID, Tracking, Carrier, CustomerID,
	CreatedBy, CreatedOn, CreateId, ModifiedBy, ModifiedById, ModifiedOn)
OUTPUT INSERTED.*
SELECT A.AssetID,
	3 AS AssetMoveID,
	R.[Store #] AS StoreID,
	25 AS TerminalID,
	R.[Out Tracking Number] AS Tracking,
	'UPS' AS Carrier,
	A.CustomerID,
	'admin' AS CreatedBy,
	GETDATE() AS CreatedOn,
	'5095aff9-7192-4b3b-a8f1-df6401973fa2' AS CreatedId,
	'admin' AS ModifiedBy,
	'5095aff9-7192-4b3b-a8f1-df6401973fa2' AS ModifiedById,
	GETDATE() AS ModifiedOn
FROM Frus AS F
INNER JOIN HotSwapOnsiteShipmentReport AS R
	ON F.PartNumber = R.[Part #]
INNER JOIN Asset AS A
	ON F.CustomerID = A.CustomerID
	AND F.FrusID = A.FrusID
	AND R.[Serial #] = A.SerialNumber
LEFT OUTER JOIN AssetHistory AS AH
	ON AH.AssetID = 1
	AND AH.AssetMoveID = 3
	AND AH.StoreID = R.[Store #]
	AND AH.TerminalID = 25
	AND AH.Tracking = R.[Out Tracking Number]
	AND AH.Carrier = 'UPS'
	AND AH.CustomerID = A.CustomerID
WHERE AH.AssetHistoryID IS NULL

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
JDay2, do you still need help with this question?
JDay2Author Commented:
Sorry for the delayed response, this is exactly what I needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.