October 24, 2007 at 3:59 pm
Hi All,
I doing a SQL Check Count like this:
SELECT COUNT(*) FROM Table1 WHERE DateSubmission IN (SELECT DISTINCT DateSubmission FROM Table2)
If the count > 0 then
Don't load the records from table1 into table2
If the count = 0 then
Load the records from table1 into table2
How can I accomplish this task in a DTS package. I have a SQL Task that only returns success or failure. I don't need that because I'll always receive a success, because it will always return a value. Would I use ActiveX script and how do I accomplish this. I'm not really looking to fail I just don't want to load duplicate data base on the date.
Thanks,
October 25, 2007 at 12:22 am
You can use EXECUTE SQL Task and inside it you can have global input/output variables...
SELECT ? = COUNT(*) FROM Table1 WHERE DateSubmission IN (SELECT DISTINCT DateSubmission FROM Table2)
WHERE the value of ? can be assigned to a global variable..
--Ramesh
October 25, 2007 at 6:52 am
Having got the result into a global variable, you will need to use an ActiveX script to interrogate the value of the global variable and then enable or disable the load step as appropriate.
Jez
October 25, 2007 at 7:24 am
Work around this problem can be to have SQL task as you have but inside SQL to raise error in one case, something like:
if (select count(*)...) > 0 raiserror('message', 16, 1)
But I don't understand why not to use your query as data source and table2 as destination? Is it possible?
October 25, 2007 at 7:33 am
Hi All,
Thanks for you input. This is how I handle this (due to time constraints) Within the sproc that loads the new data.
I created a quick check against the temp table vs source table
DECLARE @CheckCount INT
--- This is the check to determine if there are existing records
SET @CheckCount = (SELECT COUNT(*) FROM TempTable WHERE Date IN (SELECT DISTINCT Date FROM SourceTable))
If @CheckCount = 0
BEGIN
INSERT New data
END
It's a quick work around
Thanks,
October 25, 2007 at 7:42 am
SET @CheckCount = (SELECT COUNT(*) FROM TempTable WHERE Date IN (SELECT DISTINCT Date FROM SourceTable))
This statement will probably need a scan of the entire table or the clustered index....
I suggest you should change your statement to...
IF NOT EXISTS( (SELECT * FROM TempTable WHERE Date IN(SELECT DISTINCT Date FROM SourceTable) ) )
BEGIN
INSERT New data
END
--Ramesh
October 25, 2007 at 8:30 am
or even simpler, in one command (according to the first post):
Insert into table2
select table1.*
from table1 left join table2
on table1.DateSubmission = table2.DateSubmission
where table2.DateSubmission is null
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply