May 28, 2009 at 2:54 am
Hi,
I'm new to SQL and I have just created an SSIS package that imports from excel into SQL database but my problem is that i cannot seem to be able to just Add new and Update existing all it does is just add to the already existing without checking.
By the way my boss is on my case i need help now!!!!!!
May 28, 2009 at 3:06 am
The solution I would use in your scenario would be:
1. Have a staging table(with same layout as your destination table) and load everything from excel into the staging table.
2. Update the destination table from staging using a inner join between the tables.
3. Insert the new records using a left outer join between staging and destination table.
Here is a sample code:
id int
,data varchar(10)
)
CREATE TABLE #Staging(
id int
,data varchar(10)
)
--load Staging from excel using ssis
--here is some sample date
INSERT INTO #Staging
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
INSERT INTO #Destination
SELECT 1,'Z' UNION ALL
SELECT 2,'X'
UPDATE D
SET D.data = S.date
FROM #Staging S
INNER JOIN #Destination D
ON D.id = S.id
INSERT INTO #Destination
SELECT S.id
,S.data
FROM #Staging S
LEFT OUTER JOIN #Destination D
ON D.id = S.id
WHERE D.id IS NULL
-Vikas Bindra
May 28, 2009 at 8:03 am
I have quite a big excel table that will keep changing everyday and i think the staging table will be big with time, so will i not need to drop the table at some point?
May 28, 2009 at 8:07 am
You don't have to drop the staging rather you can TRUNCATE the staging after you load in destination table is completed.
-Vikas Bindra
June 3, 2009 at 6:59 am
I know this is probably to late but another option that would leverage SSIS would be to:
1) Data Source as for your excel
2) Lookup task that would load the columns needed for comparison from your destination table.
Add a column to the data flow from your destination table. Configure the Error COntrol to ignore failures.
3) Conditional split which you would split based on Null values in the column you added in the Lookup. Null Columns are new records Non Null are existing.
4) Take the output for new records to an OLE Dest that is you destination Table.
5) Take the output for existing and send it to a stage table.
6) In your work flow after this data flow have an execute SQL task which updates your Destination table based upon the staging.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply