October 21, 2017 at 10:35 pm
Good Morning,
Create table #tablesrc (VendorId int, vendorname varchar(30), brokerfee int, aliancedate datetime, active bit, matchedstatus bit)
Create table #tabletarget (VendorId int, vendorname varchar(30), brokerfee int, aliancedate datetime, active bit)
Table #tablesrc has 6 records, #tabletarget has 9 records,
now my #tablesrc each record need to loop through all records
in #tabletarget table records to see if it is matched. if matched with any records then will update matchedstatus flag to 1 else 0 in #tablesrc
please let me know using recursive CTE or cursor or while loop example
Thank you much in advance
asita
October 21, 2017 at 11:31 pm
asita - Saturday, October 21, 2017 10:35 PMGood Morning,Create table #tablesrc (VendorId int, vendorname varchar(30), brokerfee int, aliancedate datetime, active bit, matchedstatus bit)
Create table #tabletarget (VendorId int, vendorname varchar(30), brokerfee int, aliancedate datetime, active bit)Table #tablesrc has 6 records, #tabletarget has 9 records,
now my #tablesrc each record need to loop through all records
in #tabletarget table records to see if it is matched. if matched with any records then will update matchedstatus flag to 1 else 0 in #tablesrcplease let me know using recursive CTE or cursor or while loop example
Thank you much in advance
asita
This will do what you requested.UPDATE src
SET matchedstatus = CASE WHEN tgt.VendorId IS NULL THEN 0 ELSE 1 END
FROM #tablesrc AS src
LEFT JOIN #tabletarget AS tgt
ON src.VendorId = tgt.VendorId
AND src.vendorname = tgt.vendorname
AND src.brokerfee = tgt.brokerfee
AND src.aliancedate = tgt.aliancedate
AND src.active = tgt.active;
October 23, 2017 at 4:02 pm
Identifiers cannot be integers because you don't do math with them. We do not use assembly language BIT flags in SQL. Currency amounts are done with the decimal data type. The names "source" and "target" are metadata that describe how the table is used, not what it is. Can you get a book on basic data modeling or if you really want to make yourself suffer read the metadata standards? When you get that book on data modeling, you'll learn the status is from the Latin for "a state of being" and it must have a temporal dimension, shown as a start and stop timestamp for when that status was valid.
I am going to make some guesses and try and correct what you did
CREATE TABLE Vendors
(vendor_duns CHAR(9) NOT NULL PRIMARY KEY,
vendor_name VARCHAR(35) NOT NULL,
broker_fee DECIMAL (12,2) NOT NULL
CHECK(broker_fee >= 0.00));
Read Chris Date and what he has said about tables with the same structure as a huge design error. This target table should not exists at all. I’m going to guess that what you’re trying to do is recorded history of the vendors on some status variable that we have no idea about
CREATE TABLE Vendor_History
(vendor_duns CHAR(9) NOT NULL
REFERENCES Vendors(vendor_duns),
something_start_date DATE NOT NULL,
something_end_date DATE,
CHECK (something_start_date <= something_end_date),
something_status CHAR(5) NOT NULL
CHECK (something_status IN (..))
);
Now design your status encoding and let the DRI handle everything for you. There are some other idioms to keep the intervals from overlapping and gasping. You can Google the code.
>> please let me know using recursive CTE or cursor or while loop example <<
Why would you ever think of something that complicated?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply