help on updating a field in a table with the field content of another table

HI everybody need help on this..

I have two tables below

table1

country countryid

africa ___
usa ___
italy ___
Spain ___

table2

countryid country name

1 africa
2 germany
3 italy
4 usa

I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

THE RESULT WOULD BE

country countryid

africa 1
usa 4
italy 3
spain 0

thanksShow us what you've come up with so far.|||yes trying this sql and it executed well and updated the first table

UPDATE table1
SET countryid=(SELECT countryid FROM table2
WHERE cntryname=country)

thanks anyway|||the solution worked fine until it didn't encounter a duplicate value... since it is a 1 to many relationship it gave me this error...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

thanks again|||you mean you have two different countries with the same name? which ones? my geography skills aren't as good as I thought... :)|||use JOIN, something like -

UPDATE table1
SET countryid= COALESCE(table2.countryid, 0)
from table1
left outer Joint table2
ON
table2.cntryname=table1.country|||alexyeth,
I think the problem is that "africa" is not a country but continent.
:)|||thanks everybody.. the solution of mihir is great using the coalesce function...

Help on UPDATE with EXIST

I have records in seven tables that must be inserted/updated in another table. The table structures are exactally alike. The seven tables contain Aircraft position data by time. The single table is the Radars table and should contain Aircraft data by time for each cooresponding entry in each of the seven tables.

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above code works fine in the insertion of a record into the Radars when one does not already exist. The following code should update the entire row in the Radars table when a TRACKNUM is found and the record should be replaces with the corresponding one from the CG70 table for a new Time value.

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Quote:

Originally Posted by joecousins

I have records in seven tables that must be inserted/updated in another table. The table structures are exactally alike. The seven tables contain Aircraft position data by time. The single table is the Radars table and should contain Aircraft data by time for each cooresponding entry in each of the seven tables.

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above code works fine in the insertion of a record into the Radars when one does not already exist. The following code should update the entire row in the Radars table when a TRACKNUM is found and the record should be replaces with the corresponding one from the CG70 table for a new Time value.

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Update query should look like

Update [TableName]
SET [ColumnName] = [Value]
WHERE [Condition]

In your query, You have a select statement in between SET and WHERE Clause, So after SET, you Select will return some values and your WHERE clause is not being used for Update.

Also Check you SET value. It should refer to the Column you want to Update with, not to itself.sql

Help on Tables

Is there a link that has good documentation on how to create tables?
Like I'm trying to put one value in a cell like:
=IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
but it will create additional rows, because there are multiple periods.If you do not use an aggregate you will get one report row per table row...
Will Sum, or AVG, etc do what you wish...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> Is there a link that has good documentation on how to create tables?
> Like I'm trying to put one value in a cell like:
> =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
>
> but it will create additional rows, because there are multiple periods.
>|||Yeah it still doesn't work:
=SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
=SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
There are 3 periods: Period 1, Period 2 and Total that come back in the
query
The values are right but it repeats in my table
Period
Total Period 1
NETSUB 3000 1000
NETSUB 3000 1000
NETSUB 3000 1000
I just want it to appear 1 time.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> If you do not use an aggregate you will get one report row per table
row...
> Will Sum, or AVG, etc do what you wish...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Is there a link that has good documentation on how to create tables?
> >
> > Like I'm trying to put one value in a cell like:
> > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> >
> >
> >
> > but it will create additional rows, because there are multiple periods.
> >
> >
>|||From your description, it sounds like all of your fields are in the detail
row.
If this is correct, you can supress the duplicates by adding a details group
and set the group expression to use the period.
I have added a sample report to the end of this posting that demonstrates
how to use a table details group. To run it you will need access to the
Northwind sample database.
The table details group dialog can be reached from as follows: Table
Properties Dailog : Groups Tab : Details Grouping Button.
For a fuller discussion about table groups see the 'How to add a group to a
table' topic in Reporting Services Books Online.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Clark" <daveclark@.hotmail.com> wrote in message
news:usF7VwsXEHA.3292@.TK2MSFTNGP09.phx.gbl...
> Yeah it still doesn't work:
> =SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
> =SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
> There are 3 periods: Period 1, Period 2 and Total that come back in the
> query
> The values are right but it repeats in my table
> Period
> Total Period 1
> NETSUB 3000 1000
> NETSUB 3000 1000
> NETSUB 3000 1000
> I just want it to appear 1 time.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > If you do not use an aggregate you will get one report row per table
> row...
> > Will Sum, or AVG, etc do what you wish...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> > news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > Is there a link that has good documentation on how to create tables?
> > >
> > > Like I'm trying to put one value in a cell like:
> > > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> > >
> > >
> > >
> > > but it will create additional rows, because there are multiple
periods.
> > >
Table Details Group Sample
========================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="ProductID_DetailsGroup">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwind">
<rd:DataSourceID>1a2ecefc-e377-49f4-a355-2c6133f21447</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwind</DataSourceName>
<CommandText>SELECT Orders.OrderDate, [Order Details].Quantity,
[Order
Details].ProductID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY [Order Details].ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f28ffce0-dff1-4e06-ba21-c683aab528dc</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Help on SUM

Hi,

I have two tables.

table1

ID | EmpName | Salary | LocationCode
1 | aaaaaa | 1000 | 001
2 | aaaaaa | 2000 | 002
3 | aaaaaa | 3000 | 003
4 | aaaaaa | 4000 | 004
5 | aaaaaa | 5000 | 005

table2

ID | Loc_Code | Region
1 | 001,002 | 100
2 | 003,004,005 | 200

Locations inturn grouped in to Regions as shown in table2

Now i need to find sum of salaries in each region.

I have tried in this way but failed.
SELECT sum(salary) FROM table1 WHERE LocationCode in (SELECT Loc_Code FROM table2 where Region=100)

Can you please tell solution how to find sum of salares based on region.

Does the "table2" table actually contain the string '001,002' and '003,004,005' for the "Loc_Code" column? If so, can this be changed?

|||Yes, table2 contains exactly same data. It cant be changed as application is already in production.
|||

This is not a good design. While join your query performance will be very poor. Change the comma separated values into Rows.

Anyhow here the query for your current design,

Code Snippet

Create Table #table1 (

[ID] int ,

[EmpName] Varchar(100) ,

[Salary] int ,

[LocationCode] Varchar(100)

);

Insert Into #table1 Values('1','aaaaaa','1000','001');

Insert Into #table1 Values('2','aaaaaa','2000','002');

Insert Into #table1 Values('3','aaaaaa','3000','003');

Insert Into #table1 Values('4','aaaaaa','4000','004');

Insert Into #table1 Values('5','aaaaaa','5000','005');

Create Table #table2 (

[ID] int ,

[Loc_Code] Varchar(100) ,

[Region] Varchar(100)

);

Insert Into #table2 Values('1','001,002','100');

Insert Into #table2 Values('2','003,004,005','200');

Select Region,Sum([Salary]) from #table1 A

Join #table2 B on ',' + [Loc_Code] + ',' Like '%,' + A.[LocationCode] + ',%'

Group By

Region

help on select query

Hi, All
There are two tables A and B. the structure like these.
Table A
ID , Name, etc...
Table B
ID,Title,ForigenKeyToA, etc...
And one person may have multi-titles, if I join A and B, I will get
A.ID, A.Name,B.title
1, John, 'AAA'
1, John,'BBB',
2, Bill, 'AAA',
2, Bill, 'CCC'
3, Joe,'AAA'
As you can see, one person has multi-title will show multi-times, I want to
use T-SQL (not cursor) to show the mergered the multi-Title in a single row,
like these,
A.ID, A.Name,B.title
1, John,'AAA'+'BBB'
2, Bill,'AAA'+'CCC'
3, Joe,'AAA"
how acn I do this?
Any help will be appreciated, thanks
BrianHi, Brian
See: http://www.aspfaq.com/show.asp?id=2529
Razvansql

Help on query needed

Ok guys.

Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.

Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.

Thanks!basic idea is below. I had to make some assumptions about your table DDL obviously, since you didn't provide it.

select top 1
sr.sales_rep_id, count(*)
from sales_reps sr
inner join leads l on l.sales_rep_id=sr.sales_rep_id
group by sr.sales_rep_id
order by count(*) asc|||Sorry. Let me be a bit more descriptive. I havent built the tables yet, but here's what I was thinking.

Sales Rep Table
Sales_Rep_ID
Sales_Rep_Name
Sales_Group

Leads Table
Lead_ID
Customer_Name
Customer_Phone
Lead_Description
Sales_Rep_Name
Sales_Rep_ID

When the lead comes in, the Sales_Rep_Name field will either be blank, or have a default value in it. Then, I'd like to have SQL automatically assign the sales rep like I mentioned in the initial post, but (I guess) using an insert trigger.

Does this help?|||if those are your tables, then the query i posted should work.

btw, you should get rid of the Leads.Sales_Rep_Name column. it's redundant and breaks normalization as it's already in Sales_Rep.

also I wouldn't use a trigger. assuming you are inserting leads using a sproc, you can have that sproc assign the lead as well.|||OK. That helps alot. I'm pretty new to SQL, so here's another quick question if you don't mind..

The application that is managing all my leads is a proprietary app and I don't have the ability to call a sproc directly from the app. Therefore, I thought that the trigger was the way to go. I assumed the sproc must be called by the app, and that it can't invoke itself, am I right?|||if the app is calling a sproc, are you in control of the sproc? if so you could modify it to assign a lead to a sales rep.

If the app is inserting directly into a table with an ad-hoc insert statement, then you'll have to use a trigger I guess.

it sounds like you need to understand your situation better though, and figure out which of the two cases above is happening. or something else entirely perhaps.|||I'm definitely not in control of how the application is inserting the value into the table, so it sounds like the trigger is the way to go.|||not necessarily. if you own the database, and the app is calling a sproc, then you can just modify the sproc and there is no need of a trigger.

stored procedures live in sql server, not in your client app.|||Gotcha. Makes sense. Thanks for your help, and your patience. You've taught me alot.

Help on query

I have two tables Income(Date, IncomeType, Amount) and Expense(Date, ExpenseType, Amount). I want to create a balance sheet report for which need a query or view which creates a virtual table of form BalanceSheet(IncomeType, IncomeAmount, ExpenseType, ExpenseAmount). The table should look like:

Salary 100000 Rent a Car 5000
(null) (null) Tution 1000
...................................... ...
...................................... ...

How can it be done?

Appreciate for the help.

Rajuselect income, amount, '','' from incomes
union
select '','',expense, amount from expenses

Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data ve
ry
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcod
e
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.c...mance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I'm using SQL 2000. I have a simple database as backend for warehouse to
>> scan the products using access as front end. It includes 2 tables: order
>> and product.
>> Since everyday we have about 3000 orders to come in, each order has a few
>> products. The database is growing fast.
>> I can see the scanning process is a bit of slow at the front end. So I
>> kept current year data in both tables, and moved older data (3 years)
>> into historical tables within the same database.
>> Now, the active tables are smaller, do I need to do trunk database since
>> I deleted records from the active tables? Would this make it better? I
>> don't know if I can do trunk one table instead of the whole database?
>> If anyone has better idea regarding this case, it will be welcomed.
>> Thanks
>> Sarah
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data very
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcode
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

Help on Partitioning column was not found.

Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT*
FROMServer1..pTable1
UNION ALL
SELECT*
FROMServer2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.You cannot have identity columns in an updatable partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180643932.644398.247270@.g37g2000prf.googlegr oups.com...
Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT *
FROM Server1..pTable1
UNION ALL
SELECT *
FROM Server2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.|||On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Consider putting an INSTEAD OF trigger on the partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180647134.671664.320360@.a26g2000pre.googlegr oups.com...
On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Yes, <is not a permitted operator. You need to rewrite

CHECK ([ID2] <1015)

to

CHECK ([ID2] < 1015 OR [ID2] 1015)

Another story is whether this view will be very efficient. You should
probably add an index on ID2, or put it first in the primary key.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||Check out:

http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||On Jun 1, 8:12 am, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

Check out:
>
http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
"Sonny" <Sonny...@.gmail.comwrote in message
>
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>

Quote:

Originally Posted by

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.
>
Again, thank you very much for your help.

Thank you so much!!sql

Help on Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Help on Migrating from 2000 to 2005- SSIS package

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client

pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not

Thanks for ur help
Niru

You should be able to build just one package. You might need as many data-flows as there are destination tables. it depends on the situation.

Use precedence constraints to ensure that the data-flows are executed in the correct order according to RI declared on the destination tables.

If you ask more specific questions then more specific answers can be provided.

-Jamie

|||

Thanks Jamie for the clarifying ..yes i think i should create different dataflows instead of different packages .

Here is the scenario which iam working now

I have a table...i have to map some of those fields to the existing 5 tables.

fileds should map like this

source table Destination fileds with tables
(contract)

contract_code --> contract_code (Incident)

Date --> DateId (Dates)
TimeId (Times)

Duration --> Duration (Session)

Ended --> Completion_state(Session) will have options like 'yes','no','unknown'
Problem(session) -- need to set 'true' if completion_state is 'No'

From --> Number(Audit) -- Adding source if it is not existed
Id (Audit) -- giving id if you added target in the above column

To --> Number(Audit) -- Adding Traget if it is not existed
Id (Audit) -- giving id if you added target in the above column

[I will differentiate the difference betweeen 'from' and 'TO' columns in session tables seperatley by refernce]

I think you got some idea what iam trying to do ..like this i have 9-10 tables .

I apperciate if can help me out in this .

Thanks

Niru

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
SteenSteen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > I need some advises on how to join several tables and/or use "nested"
> joins
> > (if such a thing exist...)
> >
> > I trying to select some data which involves 5 tables. The relation is as
> > follows -
> >
> > Table1 should be joined with table2, table3 and table 4. Then Table 3
> should
> > be joined with Table 5.
> >
> > I've tried just using a standard "select ... from table1 JOIN
Table2...
> > JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where"
clauses,
> > but that doesn't really work.
> >
> > I think that the JOIN I'm doing with table 1 and 5 isn't correct, but
how
> do
> > I tell that I wan't to join table 3 with table5.
> >
> > I'd be happy if some of you can get me guided in the right direction on
> how
> > to do this, since I can't get my head around it.
> > In BOL it's mentioned that join expressions can be connected with AND or
> > with OR, but I can't find any examples on how to do it (...and also I'm
> not
> > sure if that will solve my problem...)
> >
> > Regards
> > Steen
> >
> >
>|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> Steen
>> SELECT col.... FROM Table1 INNER JOIN
>> Table2 ON Table1.key=Table2.key INNER JOIN
>> Table3 ON Table1.key=Table1.key INNER JOIN
>> Table4 ON Table1.key=Table1.key INNER JON
>> Table5 ON Table3.key=Table5.key
>> WHERE ..................
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> I need some advises on how to join several tables and/or use
>> "nested" joins (if such a thing exist...)
>> I trying to select some data which involves 5 tables. The relation
>> is as follows -
>> Table1 should be joined with table2, table3 and table 4. Then Table
>> 3 should be joined with Table 5.
>> I've tried just using a standard "select ... from table1 JOIN
>> Table2... JOIN Table3...JOIN Table 4....JOIN Table 5 and then my
>> "where" clauses, but that doesn't really work.
>> I think that the JOIN I'm doing with table 1 and 5 isn't correct,
>> but how do I tell that I wan't to join table 3 with table5.
>> I'd be happy if some of you can get me guided in the right
>> direction on how to do this, since I can't get my head around it.
>> In BOL it's mentioned that join expressions can be connected with
>> AND or with OR, but I can't find any examples on how to do it
>> (...and also I'm not sure if that will solve my problem...)
>> Regards
>> Steensql

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
SteenSteen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how[vbcol=seagreen]
> do
> how
> not
>|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
Steen
Steen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>
|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how
> do
> how
> not
>
|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on joining tables

Say I have three tables (just using Access for this one).

- inTheNews
- pressReleases
- events

Each for the most part, has identical columns. But, each also has one or two that are unique.

The primary key (unique identifier) in each is set to AutoNumber.

Is there a way to set it up so that the AutoNumber recognizes the numbers from the other tables and doesn't produce numbers identical to records in those tables. I would like to make sure each record in each of these tables has a unique identifier -- unique to all three tables.

Does that make sense?

Thanks.There isn't a well defined way to do this, although there are several different approaches that work reasonably well.

I think that the best way to handle this kind of problem is to create a "parent" table that has the common columns, with an autonumber as its primary key. Then create three sub-tables that have the unique columns for each of the sub-types, and create a (foreign key) relationship between these tables and the parent table.

This reduces redundancy, and it makes it easier to be sure that the autonumber values are only used by one of the sub-types. It isn't perfect, but it is probably the best you can do "out of the box".

-PatP

Help on Join

HI,

I have two tables

tbl_one
-
Call_NO(PK) | Created_dt
0001 | 6/01/2007
0002 | 6/01/2007
0003 | 6/02/2007
0004 | 6/02/2007
0005 | 6/03/2007

tbl_two
-

ID | Call_NO(FK) | Code | Updated_dt (mm/dd/yyyy)
1 | 0001 | SR | 6/07/2007
2 | 0001 | 16 | 6/08/2007
3 | 0001 | 15 | 6/09/2007
4 | 0002 | 14 | 6/10/2007
5 | 0002 | 13 | 6/11/2007

Using this tables i need to write a query to output follwing

OUTPUT :

Call_No | Created_dt | Code | Updated_dt
0001 | 6/01/2007 | 15 | 6/09/2007
0002 | 6/01/2007 | 13 | 6/11/2007
0003 | 6/02/2007 | |
0004 | 6/02/2007 | |
0005 | 6/03/2007 | |

Conditions are for each call we need to take "code" using last updated_dt. For ex: In tbl_two we have three records for call no 001 in this we need to take last updated date ie, 6/09/2007 and join with tbl_one on call_no and show records as shown in output.

Please help me to write this query,

Here it is,

--Code Snippet

Create Table #tbl_one (

[Call_NO] int ,

[Created_dt] datetime

);

Insert Into #tbl_one Values('0001','6/01/2007');

Insert Into #tbl_one Values('0002','6/01/2007');

Insert Into #tbl_one Values('0003','6/02/2007');

Insert Into #tbl_one Values('0004','6/02/2007');

Insert Into #tbl_one Values('0005','6/03/2007');

Create Table #tbl_two (

[ID] int ,

[Call_NO] Varchar(100) ,

[Code] Varchar(100) ,

[Updated_dt] datetime

);

Insert Into #tbl_two Values('1','0001','SR','6/07/2007');

Insert Into #tbl_two Values('2','0001','16','6/08/2007');

Insert Into #tbl_two Values('3','0001','15','6/09/2007');

Insert Into #tbl_two Values('4','0002','14','6/10/2007');

Insert Into #tbl_two Values('5','0002','13','6/11/2007');

Code Snippet

Select

A.[Call_NO]

, A.[Created_dt]

, C.[Code]

, B.[Updated_dt]

from

#tbl_one A

Left Outer Join

(

Select

[Call_NO]

,Max([Updated_dt]) [Updated_dt]

from

#tbl_Two

group by

[Call_NO]

) as B

On A.[Call_NO] = B.[Call_NO]

Left Outer Join

#tbl_Two C

on B.[Updated_dt] = C.[Updated_dt]

and B.[Call_NO] = C.[Call_NO]

|||SELECT t1.Call_No, t1.Created_dt, ISNULL(t2.Code, '') as Code, ISNULL(t2.Updated_dt, '') as Updated_dt
FROM tbl_one t1
LEFT JOIN (SELECT ttwo.Call_NO, ttwo.Code, ttwo.Updated_dt

FROM tbl_two ttwo

(SELECT Call_NO, MAX(Updated_dt) LastUpdate FROM tbl_two GROUP BY Call_NO) lstUpd
ON ttwo.Call_NO = lstUpd.Call_NO
AND ttwo.Updated_dt = lstUpd.LastUpdate) t2

ON t1.Call_NO = t2.Call_NO

help on indices

are there any thumb rules to follow to set up indices on tables and to set the fill factor. can somebody suggest any link where I can read about this.
thanks in advance.
RohitSQL Server Central has some real good articles. Unfortunately, I haven't found any standard guidlines for creating indexes and setting fill factors. It realy depends how your system is designed and what you're trying to do. I found testing is the key and learning simply by trial and error.

Heer's the link:
http://www.sqlservercentral.com/|||The biggest factor I have found is the placement of the clustered index. I typically place the clustered index on the identity column (if I have one) so I don't have to worry about pagesplitting as new data always falls at the bottom of the leaf level. You're big thing with fill factors is you are paying a price in storage to reduce or eliminate page splits during operations. Kalen Delaney's Inside SQL Server 2000 is good for researching indexing and page splits.

HTH|||Page splitting is really what can eat time. But that only happens when a new key-value is inserted or changed "at random" or when a record-delete occurs. It really depends on your data and your application. In an awful lot of applications, deletions and even key-changes occur "almost never."

I'd suggest that you make no changes at all unless, after quite some time of running the database, you can establish that insert/delete/change-key activity is really creating a "human noticeable" performance problem. Establish that; don't "fiddle and tweak and guess."

Another thing to really bear in mind is that every index you place on the database ought to have a clear purpose for being there. It's real easy to have too many, and I think that's what really munchie-munchies the time away for any database. Some indexes might need to exist only for certain purposes, e.g. once-a-month, in which case it might be profitable to drop or deactivate them except when they're needed. (A computer can rebuild an index "from scratch" uncommonly quickly. Obviously if you are dealing with "billions and billions" of records, you might not... etc.)

An index will never be "optimal." Not at any time; not if it's being used. :p They don't need to be reorganized frequently... they will maintain themselves. Let them do that! This means that they will always have fairly-empty pages, somewhat lopsided record-counts in the tree... and "who cares." You should really only plan to take action when, and if, you factually establish the presence of unacceptable pain in the current status quo.|||The reason you would care about the amount of empty space in your indices are that it makes your queries have higher logical IO than neccessary.

To see if you have page splits issues, run perfmon and check your page splits/sec. To check your fragmentation level of your tables run dbcc showcontig, to see your logical IO and how you can tune your queries, use "set statistics IO on"

HTH|||Now I know where to start from, thank you all for the inputs.
Rohit

Help on delete statement

Hi,
I have two tables.
Table1:
MySymbol, BloombergSymbol
A, A_Bloomberg
B, B_Bloomberg

Table2:
MySymbol, Open, High, Low, Close
A,...
A,...
B,...
B,...

I want to perform one task--Delete all the records in table2 whose MySymbol matches one given BloombergSymbol in table1.

3x.which dbms is this?|||Try this query:

Delete Table2.* from Table1 LEFT JOIN Table2 ON Table1.MySymbol = Table2.MySymbol|||Alternate syntax :

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.|||sql server
which dbms is this?|||this one works.
thank you guys.

Alternate syntax :

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.|||For SQL Server use:

DELETE table2
FROM table2
INNER JOIN table1 ontable2.MySymbol = table1.MySymbol

Help on Database Design

I would like to design some tables for offices. In the scenario, there are so many offices which are categorized by different levels. Eg. National level, State level, District level etc... The design must satisfy the future need of adding another intermediate level (say, Zone level in between National and State), without any redesign of database. So how could I materialze this?In one table you can create a column with CHAR(5) that specifies the category of level that can be used as a master table to specify these categories with a future addition of sections.