DECLARE @INSERTED_COUNTRIES table
(
country varchar(100),
country_id int
)

INSERT INTO DIM_COUNTRY
OUTPUT INSERTED.country,INSERTED.country_id INTO @INSERTED_COUNTRIES
SELECT
Usage,country,region,ownership,enddate,users,sbu,sbg,sbe,buildingOrigin
FROM
SAMPLE s
LEFT JOIN DIM_COUNTRY c
ON c.country = s.country
WHERE c.country IS NULL


INSERT INTO FCT_DETAILS 
SELECT ic.country_id,s.lid,s.Name,s.address,s.city,s.state,s.[area in sqft],s.latitude,s.longitude 
FROM
SAMPLE s
JOIN @INSERTED_COUNTRIES ic
ON ic.country=s.country
LEFT JOIN FCT_DETAILS fd
ON fd.lid = s.lid
WHERE fd.lid IS NULL

-- Insert Dimension
INSERT -- DISTINCT??
    DIM_COUNTRY
SELECT
    s.Usage,
    s.country,
    s.region,
    s.ownership,
    s.enddate,
    s.users,
    s.sbu,
    s.sbg,
    s.sbe,
    s.buildingOrigin
FROM
    SAMPLE AS s
LEFT JOIN 
    DIM_COUNTRY AS c
    ON c.country = s.country
WHERE 
    c.country IS NULL


-- Insert Fact
INSERT
     FCT_DETAILS 
SELECT --DISTINCT??
    c.country_id,
    s.lid,
    s.Name,
    s.address,
    s.city,
    s.state,
    s.[area in sqft],
    s.latitude,
    s.longitude 
FROM
    SAMPLE AS s
INNER JOIN
    DIM_COUNTRY AS c
    ON c.country = s.country
LEFT OUTER JOIN
    FCT_DETAILS fd
    ON fd.lid = s.lid
WHERE 
    fd.lid IS NULL

quote:

---

Originally posted by 7siva7

can you tell me what you used in the sample code you wrote?

HI

---