to insert...or not to insert..that is the question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

to insert...or not to insert..that is the question

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-01-29 : 14:27:28

I am adding to a table on a weekly basis with a sql job script. The data source could be new, could be an update, or could be the same as last week. I would like to modify my 'insert' logic to check to see if that weeks data is already there and just update the existing record rather than inserting a new record. The combined key to know if the record exists is MetricID & ReportWeek (there should be only one record for a metric in a certain reportweek.)

Insert scorecard.scorecardmetrics(MetricID,Reportweek,Target,Value1,Value2,MetricValue,Entered)

SELECT 40 as MetricID --
,DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()) AS ReportWeek
,.8 as Target
,COUNT(AgencyCode) AS Value1
,15 as Value2
,COUNT(*) / 15. AS MetricValue
,GETDATE() AS Entered
FROM Confirm
WHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)

If I can make the decision to update, rather than insert, then GETDATE() will update field 'Update' rather then setting the 'Entered' field.

Thanks for the help!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 15:28:27

Search for Lookup transformation Task in here.

Nagaraj
Starting Member

14 Posts

Posted - 2009-01-30 : 04:38:24

As Sodeep said you can use LookUp transformation in order to upload the new record.
And also you can use Slowly Changing Dimension (SCD) to load the new record

Nagaraj.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-30 : 07:02:31

You can also do:
1. Step: update by using INNER JOIN source and destination tables
2. Step: insert with adding NOT EXISTS to your WHERE-Clause

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:56:38

quote:
Originally posted by dlorenc

I am adding to a table on a weekly basis with a sql job script. The data source could be new, could be an update, or could be the same as last week. I would like to modify my 'insert' logic to check to see if that weeks data is already there and just update the existing record rather than inserting a new record. The combined key to know if the record exists is MetricID & ReportWeek (there should be only one record for a metric in a certain reportweek.)

Insert scorecard.scorecardmetrics(MetricID,Reportweek,Target,Value1,Value2,MetricValue,Entered)

SELECT 40 as MetricID --
,DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()) AS ReportWeek
,.8 as Target
,COUNT(AgencyCode) AS Value1
,15 as Value2
,COUNT(*) / 15. AS MetricValue
,GETDATE() AS Entered
FROM Confirm
WHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)

If I can make the decision to update, rather than insert, then GETDATE() will update field 'Update' rather then setting the 'Entered' field.

Thanks for the help!

see this

http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-02-02 : 14:47:18

Yes...the referenced help quite a bit..thank you...here is my resulting code, but I think it can be optimized?... for example, is there a way to specify a 'foreign-key' composed of the metricID and the Reportdate?...

for the purposes of this query, if I can make each record unique with these two columns(a combined key?), then an insert will fail, and I can just fall thru to an error handler that will do the update..??...

if not exists (select * from epmportal.epmoscorecard1 where metricID = 40 and convert(char(8),ReportWeek,101) = convert(char(8),DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()),101))

begin
insert into epmportal.epmoscorecard1 (MetricID,ReportWeek,Target,Value1,Value2,MetricValue,Entered)
SELECT 40 as MetricID -- Confirmed Agencies
,DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()) AS ReportWeek
,.8 as Target
, COUNT(AgencyCode) AS Value1 -- Total Agencies Confirmed
, 15 as Value2 -- Targeted Consolidated Agencies
, COUNT(*) / 15. AS MetricValue
, GETDATE() AS Entered
FROM Confirm
WHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)
end

else

update epmportal.epmoscorecard1
set ReportWeek=DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE())
,Target=.8
,Value1=(select COUNT(*) from confirm where (Action = 'fy-10 Budget') AND (Confirmed = 1) and (MetricID=40))
,Value2=15
,MetricValue=(select COUNT(*) from confirm where (Action = 'fy-10 Budget') AND (Confirmed = 1) and (MetricID=40)) / 15.
,Entered=GETDATE()
FROM Confirm
WHERE (Action = 'fy-10 Budget') AND (Confirmed = 1) and (MetricID=40)

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-02-05 : 14:40:22

ok...next step has me stumped..I want to take that sql script and convert it into an ssis script using BIDS...What widgets from the tool box would should I use?
1. I read data from one database, do a summation and calculation to produce a single record that is the new metric for the week.
2. Now I need to read the target datamart, do a search to see if the metric and reportweek exist, if they do then I update the metric; if they do not I insert a new record.

So far, I have my two databases in the connection manager (Portal, and ScoreCard), I have a dataflow task (on the control flow tab). The dataflow task opens into the dataflow tab, where I have an OLE DB Source that I pasted the sql code into...but because the sql code references both the source and target databases, the preview errors out.

I am guessing I dont know how to reference the connection manager databases from the sql code?

or I need to split the code into different containers that work in this GUI..??...

thanks for the advice!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:59:16

what you need is oledb source to read data from one database with sql command as type. the query used should do a summation and calculation to produce a single record that is the new metric for the week.
for second part, just use lookup transformation which selects records from destination table. then give lookup columns as metric and reportweek. connect success output to OLEDB destination with UPDATE query. set on failure property to redirect row for lookup task. then link error output again to OLEDB destination and give query as INSERT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources