November 9, 2007 at 8:58 am
My current ssis package will perform data transfer from Source Table A to Target Table B. I want the ssis package to check if Target Table B has any records; if no records, perform regular data transfer else perform data transfer from Source Table A to Target Table C.
November 9, 2007 at 9:37 pm
You can use an Execute SQL Task for this in the control flow. Execute a SELECT COUNT(*) FROM Table, and store the resulting count in a variable. Create a second variable that has the EvaluateAsExpression property set to TRUE, and add an expression to it like @[User:RecordCount] = 0 ? "TableB" : "TableC".
In the data flow, change the OLE DB Destination to get the table name from the variable you defined above.
Let me know if you need more detail.
November 15, 2007 at 12:11 pm
Yes, I would like more detail using the variable conditioning as previously mentioned. Thanks again, Rich
November 16, 2007 at 10:46 am
For "...check if Target Table B has any records; if no records...", I would use select top 1 columnname from the table and check rowcount = 0 or not to see whether "any" record in the table.
Using select count(*) costs a lot. If you do not need to know how many, no need to use it.
November 18, 2007 at 2:55 pm
I posted a sample and walkthrough here. http://agilebi.com/cs/blogs/jwelch/archive/2007/11/18/changing-destination-tables-with-the-ole-db-destination.aspx
November 26, 2007 at 5:44 pm
An alternative way to check if a table has records, if you don't need to know the rowcount:
[font="Courier New"]IF EXISTS (SELECT * FROM mytable)
SET @loadtable=0 ...[/font]
In my quick tests, it showed a table scan in one case and an index scan in the other, but because the estimated row count is always only one (it's just scanning until it finds one, any, row), the cost is very low.
Bill
November 26, 2007 at 10:21 pm
Absolutely true, its a proper way of checking whether there is any data exists in the table or not.
November 27, 2007 at 4:29 am
Bill Nye (11/26/2007)
An alternative way to check if a table has records, if you don't need to know the rowcount:[font="Courier New"]IF EXISTS (SELECT * FROM mytable)
SET @loadtable=0 ...[/font]
Try to use Row Count component instead.
November 27, 2007 at 12:53 pm
Marek,
Why use "Row Count component"? Do you mean @@rowcount, or are you suggesting something else? Note that the request was to determine whether a table had rows at all - the count wasn't actually needed.
A quick Google to confirm my suggestion led me to:
which led me to:
So, to "check if table has records" I still say use "exists".
Bill
November 27, 2007 at 1:29 pm
The RowCount component is specific to SSIS - it's one of the transformation components available in the data flow.
However, it's not really appropriate for this scenario, since to use it, you'd have to run a seperate data flow task, select rows from Table A, then run those through the RowCount transform. Much faster to use an Execute SQL task to run a "IF EXISTS("...
November 28, 2007 at 1:27 am
Bill Nye (11/26/2007)
[font="Courier New"]IF EXISTS (SELECT * FROM mytable)SET @loadtable=0 ...[/font]
It is not good practice to issue SELECT * FROM statement (btw it can take the long time for the huge table). I think the best way to achieve this is the following statement (assuming that id is the primary key for this table):
[font="Courier New"]IF EXISTS (SELECT TOP 1 id FROM mytable WHERE id IS NOT NULL)
SET @loadtable=0 ...[/font]
November 28, 2007 at 3:53 pm
Generally speaking that is absolutely correct that a "select *" will be expensive. However, in this case the "Exists" keyword negates that effect, because apparently it tells the query engine to not just look for the top 1, but simply any 1 row that it finds. The results and query plans for the following are identical:
[font="Courier New"]IF EXISTS (SELECT TOP 1 * FROM mytable)
Print 'Exists!'
GO
IF EXISTS (SELECT TOP 1000000000 * FROM mytable)
Print 'Exists!'
GO
IF EXISTS (SELECT * FROM mytable)
Print 'Exists!'[/font]
In all cases the estimated number of rows in the table/index scan is 1. So, it makes no difference for performance.
By the way, even though there were not 1000000000 rows in my table it still printed "Exists!" because a result existed from the query.
December 13, 2011 at 2:22 pm
IF(Exists(Select top 1 myColumn from myTable))
do something...
ELSE
do something else...
This will always return 1 row and 1 row only no matter what.
It can be indexed or non-indexed, null or not null.
If a row exists in myTable this is the fastest way to determine if myTable
has content.
December 14, 2011 at 12:17 am
johnmcpherson1 (12/13/2011)
IF(Exists(Select top 1 myColumn from myTable))do something...
ELSE
do something else...
This will always return 1 row and 1 row only no matter what.
It can be indexed or non-indexed, null or not null.
If a row exists in myTable this is the fastest way to determine if myTable
has content.
It doesn't matter if it says IF EXISTS (SELECT TOP 1 ...)
or if it says IF EXISTS (SELECT * ...)
Execution plans are exactly the same for both queries. http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/
But what about sp_spaceused? It gives you a rowcount without placing a lock on the table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 2:27 am
Powichrowski Marek (11/28/2007)
It is not good practice to issue SELECT * FROM statement (btw it can take the long time for the huge table). I think the best way to achieve this is the following statement (assuming that id is the primary key for this table):
[font="Courier New"]IF EXISTS (SELECT TOP 1 id FROM mytable WHERE id IS NOT NULL)
SET @loadtable=0 ...[/font]
Just for interest, there is a secondary point regarding this suggested code: if ID is the PK, then by definition it can never be NULL, so the WHERE condition is superfluous.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply