Link to home
Start Free TrialLog in
Avatar of JDay2
JDay2

asked on

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

User generated image
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?
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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"?
Avatar of JDay2
JDay2

ASKER

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.
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
Avatar of JDay2

ASKER

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?
Yes you could create a trigger or a scheduled job to handle similar logic.
Avatar of JDay2

ASKER

How do I write a trigger/stored procedure to accomplish instead of ssis?
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?
Avatar of JDay2

ASKER

Attached are the files requested.  If you need anymore information, please let me know.

Thanks.
script.sql
test.sql
JDay,

I have gotten a little busy lately...I will try to get you something tomorrow.
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,
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

Avatar of JDay2

ASKER

[createdby] =admin
[createdon] =currentdatetime
[createid] =5095aff9-7192-4b3b-a8f1-df6401973fa2
[modifiedby]=admin
[modifiedbyid]=5095aff9-7192-4b3b-a8f1-df6401973fa2
[modifiedon]=currentdatetime
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
JDay2, do you still need help with this question?
Avatar of JDay2

ASKER

Sorry for the delayed response, this is exactly what I needed.