T-SQL Question

Transact-SQL
1

I am importing data from Flat file using SSIS. I created a table named: _Vendors with the following column name:
[Vendor_Id], [Name1], [Vendor_Status], [Vendor_Persistence], [Vendor_Class], [WTHD_SQ]

When ever i load data daily if the vendor_id is different insert. If vendor_Id is same but the Vendor_Status is changed update. Is there a way to tackle this.

2 
insert into _Vendors ...
select from ... newdata
where not exists 
 (
    select 1 from    _Vendors
    where _vendors.Vendor_Id = newdata._VendorId
);

update v
set ...
from _Vendors
join newdata on _vendors.Vendor_Id = newdata._VendorId
3

In order:

  1. Load the flat file into a staging table. Since you want to use the table twice, I'd suggest a non-temp table name in tempdb, such as tempdb.dbo._vendors_import
  2. Update the rows that need updated.
  3. Insert new rows.

--2
UPDATE v
SET Vendor_Status = vi.Vendor_Status
--, ...set other column(s) if needed
FROM tempdb.dbo._vendors_import vi
INNER JOIN dbo._Vendors v ON v.Vendor_Id = vi.Vendord_id AND ISNULL(v.Vendor_Status, '-1') <> ISNULL(vi.Vendor_Status, '-1')
--3
INSERT INTO dbo._Vendors ( ... )
SELECT ...
FROM tempdb.dbo.vendors_import i
WHERE NOT EXISTS ( SELECT 1 FROM dbo._Vendors v WHERE v.Vendor_Id = vi.Vendor_Id)

4

I would investigate using SCD directly in SSIS for this...that is what it is designed to do.

5

Short for Transaction-SQL, an extended form of SQL that adds declared variables, transaction control, error and exceptionhandling and row processing to SQL

The Structured Query Language or SQL is a programming language that focuses on managing relational databases. SQL has its own limitations which spurred the software giant Microsoft to build on top of SQL with their own extensions to enhance the functionality of SQL. Microsoft added code to SQL and called it Transact-SQL or T-SQL. Keep in mind that T-SQL is proprietary and is under the control of Microsoft while SQL, although developed by IBM, is already an open format.