Table Scans - More Rows Brought Back Than In Table

Jul 20, 2005

I have a query which is quite complex. It's based on a set of data in
a complex view which takes the data from several tables.

In this complex query, if I allow the various parts of the query to
work on the results of the view (MISView), it can take 15 minutes to
run (eek !), however, if I create a temporary table with the data from
the view and then use that for the remainder of the query, it runs in
approx 20 seconds.

Now, I have examined the execution plan (my new favourite toy) and
there is a difference (as expected). However when looking at the part
of the query that takes up most of the time, it shows that it brings
back 109,645,866 records from a table (Credit) that contains 13,002
records. This table is one that is referenced in the view (MISView)
which contains 13,653 records and does get some of it's data from the
table which is scanned (Credit).

For the record, we don't have any tables with over 100,000 records in,
so 109 million rows is going some for us. The part of the query that
runs slow does reference another copy of itself but this is necessary
for the equation that is being run.

Now I'm OK with why it's doing the table scan, but why does it bring
back substantially more data than is in the table ? Is it some
multiple of the number of records that it's trying to work out. I
assume it tries to run a seperate plan for the view as part of it's
process.

Ideally, I'm still going to go down the route of the temporary table,
but I would like to understand more about what it does first as I
don't like leaving things unanswered.

Any help would be appreciated.

View 6 Replies


ADVERTISEMENT

How To Monitor For Full Table Scans?

Jul 20, 2005

How-doody all. Is there an easy way to identify tables that are notproperly indexed based on how often sessions are performing full tablescans on it?

View 1 Replies View Related

Behavior Of SQL Server On Table Scans

Jun 29, 2007

Hi All,

if someone can point me to documentation on this I would appreciate it.....



If there isn't any....

I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.



This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in?



Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....



It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do?



Thank You in Advance



Peter

www.peternolan.com

View 2 Replies View Related

Does Sql Server Collect Stats On Full Table Scans?

Jul 20, 2005

Hi all. Anyone know if sql server collects stats on how many fulltable scans a table gets and if so how do I get at those stats? Tryingto track down poorly indexed tables / processes and I am guessing thatsql server does have this data secreted away somewhere much like mydb2 and informix databases do.

View 1 Replies View Related

Problem With Update When Updating All Rows Of A Table Through Dataset And Saving Back To Database

Feb 24, 2006

Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify  a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType = CommandType.Text;
snUpdate.CommandText = "Update TestTable set shipdate = @shipdate";
snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds, "Table1");
conn.Close();
 
-Thanks

View 4 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 
 

View 3 Replies View Related

Accidentally Deleted A Table In My Local SQL Server How Can I Get Back The Table

Jan 13, 2004

Hi,

I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!

Thanks in Advance

View 1 Replies View Related

Count All Table Rows Then Insert Into Test Table Using SSIS Packages

Jul 15, 2013

I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .

test99: tableName countRows
t1 20
t2 30
t3 25

View 2 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE

Dec 7, 2007

I have the following data

MASTER
id
name


DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 Replies View Related

SQL Call To Count The Total Rows In Table B For Each User In Table A

Jan 17, 2006

I have 2 tables:
 
TableA:
Name
UserA
UserB
UserC
 
Table B:
Name               Data
UserA              xxx
UserB              asdasd
UserB              ewrsad
UserC              dsafasc
UserA              sdf
UserB              dfvr4
 
I want to count the total entries in Table B for every user in Table A.  The output would be:
 
Name               Count
UserA              2
UserB              3
UserC              1
 
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A.  Also, Table A is dynamic, so the users are always changing.  Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?

View 5 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

Copying All Rows From One Table Into Another Existing Table And Overwriting Data

Feb 15, 2005

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanks

View 1 Replies View Related

Trigger On Table LeaveRegister And Inserting Rows In Audit Table

Oct 22, 2012

I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.

View 6 Replies View Related

How To Archiv Table To Another Table With Unique Number For All Rows Once + Date

Apr 28, 2008

need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )

this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03

for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15

.........................
.....................................................................667 15/04/2008 17:15


next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15

.........................
.....................................................................668 15/04/2008 08:15



TNX

View 3 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:


SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x


This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:


SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'



'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

SQL Server 2012 :: Create Table From Rows Of A Different Table

Mar 6, 2015

I am using SQL SERVER 2012..I have the following table:

tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |

I want to creare a new empy table with the columns name contained into the column B of the first table; the following should my results:

tbtab2
| Pluto | Pippo | Rossi |

View 9 Replies View Related

How To Insert Rows To Table B Based On Values In Table A.

Mar 7, 2008



I need Insert rows in the OrderDetails Table based on values in the Orders Table

In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType


For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD



Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.


I need a SQL Script to accomplish this. thanks

View 6 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table

Jan 19, 2008

Code Block


Hi,

I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:

Table Name: metrics_ladder

id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.

Table Name: metric_details

id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2

Table Name: metric_type_details

id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated

The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:

id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>

Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.

Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,

metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally

metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).

As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:

security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.

which is then unpivoted.

The SQL that achieves this is more or less as follows:

*********
START SQL
*********

declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)


-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;

with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id

*********
END SQL
*********

The result then needs to be unpivoted and stored in metrics_ladder.

And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.

Many thanks (if you've read this far!)

M.

View 6 Replies View Related

Selecting SUM From One Table As Field In Results Of Rows From Another Table.

Nov 20, 2007

Hi,
These aren't my tables, but lets dumb it down for my sake!

I have 2 tables:

CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount

There are several records in TransactionInfo for each record in CompanyInfo.

I want to:

SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)

How can I do this? Can I?

View 4 Replies View Related

Copy Rows To The Same Table And Its Related Data In The Other Table

Nov 23, 2007

Hi All,
I have 2 tables People & PeopleCosts.

PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber

The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..

Kindly help. thanks in advance.
Myl

View 3 Replies View Related

Transact SQL :: Update Table From Another Table For Multiple Rows

Jun 10, 2015

Matrix table has ID column and data below.

ID        Flag     TestDate         Value     Comment                                                                  
111          2       12/15/2014     7.5             null
222         2            Null                10          received

Matrix_Current table could have 1 or multiple rows as below.

ID        Flag               TestDate           Value         Comment
111         2                  01/26/2015        7.9                                                                      
111         2                  02/23/2015        7.9                                                      
111         2                  04/07/2015        6.8
222        1                   null                   8               test comment 1
222        3                   null                   9               test comment 2

When I run below update

 UPDATE  AM
 SET  M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
 FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID

Matrix table has value below:

ID        Flag     TestDate         Value     Comment                                                                  
111          2       01/26/2015      7.9             
222         1            Null               8            test comment 1

I want to update Matrix table from all row from Matrix_Current, final table would like below:

ID        Flag     TestDate        Value     Comment                                                                  
111          2        04/07/2015      6.8             
222         3            Null                9         test comment 2

View 3 Replies View Related

Table Back Up

Apr 14, 2008

Please suggest me how to take table back up from a database.

View 6 Replies View Related

Back Up Table

Mar 17, 2008

dear db gurus,

can you help me how to get back up for
the table in a database...

regards
dass

View 4 Replies View Related

Moving Lots Of Rows From Table To Another Table

Jan 17, 2008

Hi all,



I have two tables within the SQL server and there is an application writes 20.000 or 40000 rows in the First table.

1) I need to know when the table completes filled to start move the data to the second table.

2) After I copy the rows I need to delete the rows from the first table.



I though to use the trigger but I am not sure if this is the best approach or not





Please help....



Thank you

sms

View 11 Replies View Related

Remove Rows From One Table That The Key Does Not Exist In Another Table

Feb 15, 2008



I need a SSIS Package that compares the two tables and removes the rows in the first table with keys that do not exist in the second table. For example....

I have a table of returns based on returnID. In another table I have returnErrors that are based on returnID as well. I want a package that will uses my returns table as a source and compares that dataset to the dataset of the returnError and remove or spilt the data so that my remaining dataset only has returns that have returnErrors. I can do this in T-SQL, but I am looking for a SSIS solution that uses the conditional split transformation or some other transformation(s) combinations.

-- Ryan

View 7 Replies View Related

Select ROWS In Table A That Aren't In Table B

Jan 28, 2008

I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.

I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).

Suppliers_To_Categories contains which suppliers are related to which categories.

The set-up is so (simplified):

Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4

Categories
id
1 Category A
2 Category B
3 Category C
4 Category D

Suppliers_To_Categories
id supplierId categoryId
1 1 1
1 1 2
1 1 3
2 2 3
3 3 1
3 3 3


What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.

Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...

I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
SELECT Suppliers.*
FROM CatToSupplier INNER JOIN
Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
WHERE (CatToSupplier.CatId = @CatId)

This is probably really easy, but I've become unstuck!

View 13 Replies View Related

How To Back Up Table In SQL 2000

Jul 6, 2001

How to Back up table in SQL 2000

View 3 Replies View Related

Bring A Table Back

Feb 25, 2004

Hello, everyone:

I have deleted a static table accidentally in production. How will I bring it back? Thanks a lot.

ZYT

View 2 Replies View Related

How To Back Up Certain Table Or Files

Mar 31, 2008

hello everyone... i need ur help... how can i back up only a certain table that i want using sql server management studio?? or mbe some commands...???

View 5 Replies View Related

How To Back Up Certain Table Or Files

Mar 31, 2008

hello everyone... i need ur help... how can i back up only a certain table that i want using sql server management studio?? or mbe some commands...???

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved