Query/View Question

I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:

TABLE 1

PrimaryKey

Textfield1

Textfield2

Textfield3

TABLE 2

PrimaryKey

Table1ForeignKey

Table3ForeignKey

Textfield1

TABLE 3

PrimaryKey

Textfield1

Textfield2

Textfield3

Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:

Table 1

PrimaryKey

Table1

Textfield1

Table2

Textfield

Table3

Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

Hi,

some more questions:

how do you define "the latest" in table2 ?

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||

Perhaps my question will make more sense explained like this:

I will use an analogy of checking out books from the library.

Table 1 is a table of books, with a primary key of bookid.

Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.

Table 3 is a table of library card holders contact info with a primary key of CardholderID.

All of the primary keys are auto-incrementing.

I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.

I hope that makes more sense.

|||

OK, keeping your analogy in mind, the query should be like:

Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK

untested....

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

query, special transpose and merge of data

hi all,

i'm a newbie with a big problem :). I want to create a query, which creates data in a merged and transposed way.

i have 3 tables, but only two of them are interesting for me now.

Table0:
F_id, prop1, prop2 ...
-------
10, x, y
20, v, d

Table1 (m:n Table, connecting Table0 with Table 2):
F_id, V_id
----
10, a
10, b
20, a

Table2:
V_id
--
a
b
c
d

the sql should create this result:
SQL Result:
F_id, a, b, c, d
-----
10, 1, 1, 0, 0
20, 1, 0, 0, 0

the tupels of Tabl2 with V_id should be transposed as columns names of the sql result.
Then every entry in the Table1 (m:n-Table) should insert a '1' in the column of the corresponding V_Id, otherwise if there is no connection between Table0 and Table2 in the m:n-Table, then there is a '0' to be inserted.

In the moment i have no clue, i read a lot about transposing and crosstab things, but that was no help for my special problem.

I appreciate any help.
Thanks!select T0.F_id
, sum(case when T1.V_id = a
then 1 else 0 end) as a
, sum(case when T1.V_id = b
then 1 else 0 end) as b
, sum(case when T1.V_id = c
then 1 else 0 end) as c
, sum(case when T1.V_id = d
then 1 else 0 end) as d
from Table0 as T0
inner
join Table1 as T1
on T1.F_id = T0.F_id
group
by T0.F_id|||Thanks. But the problem is that the Table1-entries are dynamic or the number of entries are variable.

Maybe i correct sth; its not really important to get all of these Table1-entries, but all entries from the m:n Table should be inserted.|||thanks, but you should have mentioned that in your initial post

would have saved me wasting my time writing sql that you can't use

:)|||Besides, this looks like a clasical "homework" assignment -- what have you done yourself to solve the problem? :mad:|||lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

which is probably why he was posting|||if the number of entries is variable, then this problem cannot be done with just sql
It can, by using recursive SQL.
(Of course, one cannot return a "variable" number of columns, but a column can be returned which contains a variable amount of concatenations of expressions.)|||It can, by using recursive SQL.oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary

:)|||oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary
WITH T(F_id, aux, V_id) AS
( SELECT Table0.F_id,
MIN(Table1.V_id),
COALESCE(T.V_id, '') || ', ' || MIN(T1.V_id)
FROM Table0 AS T0 LEFT OUTER JOIN T ON T0.F_id = T.F_id
INNER JOIN Table1 AS T1 ON T0.F_id = T1.F_id
WHERE T.aux IS NULL or T1.V_id > T.aux
GROUP BY Table0.F_id
)
SELECT F_id, V_id
FROM T

Didn't test it, so there could be some minor tweaks ...)|||that's mighty impressive, i like it

but frankly, i get lost when i try to understand what it's doing

:)|||thanks a lot.
i tried to get it work, even though i didnt get it completely. I need a little time for it.|||I've tested the following and it works:
create table T0 ( f int ) ;
create table T1 ( f int , v char(1) ) ;
insert into T0(f) values(10) union all values(20) ;
insert into T1(f,v) values(10,'a') union all values(10,'b') ;
insert into T1(f,v) values(20,'b') union values(20,'c') union values(20,'d');

with T (f, v, aux) AS
(SELECT f, CAST('' AS varchar(255)), CAST(null AS varchar(255)) FROM T0
UNION ALL
SELECT T.f, T.v||', '||coalesce(T1.v, ''), coalesce(T1.v, '')
FROM T, T1
WHERE T.f = T1.f AND coalesce(T.aux, '') < T1.v
)
SELECT f, substr(v, 3)
FROM T AS Tx
WHERE length(v) = (SELECT max(length(v))
FROM T
WHERE T.f = Tx.f)
Quick explanation:
The "recursive" table T is built up as follows:
- First it's given all rows of table T0, i.e.
10, '', ''
20, '', ''
- Then the join of this table with T1 is added. The result is
10, '', ''
20, '', ''
10, ', a', 'a'
10, ', b', 'b'
20, ', b', 'b'
20, ', c', 'c'
20, ', d', 'd'
- This last step is iterated, but such that only rows of T and T1 are considered to be joined if T.aux (last column) is strictly smaller than T1.v .
Hence the following rows are added to T in step 3:
10, ', a, b', 'b'
20, ', b, c', 'c'
20, ', b, d', 'd'
20, ', c, d', 'd'
Finally (for the small tables used here) the row
20, ', b, c, d', 'd'
is added.
With this table T, the actual query (SELECT f, substr(v, 3) FROM T) is executed. The "substring" removes the leading ", " while the "WHERE" condition only keeps the longest strings in v, per f, i.e. the result is:
t | v
-- + ---
10 | a, b
20 | b, c, d|||So Sorry, that i didnt thanked you!
Thanks a lot Peter!!!! This was helping me out!!!!!!

query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakriWould the answer in this case be 650?|||it will be 650 and 700 according to user id|||Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).|||Ok - you edited your typo while I was posting :D

So - why two answers?|||ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.|||Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH|||i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.|||What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

-PatP

Query within a query

Hi, I am having some logic trouble... I think I want to create a Left Join but I am not sure here is my query...

SELECT Nature.Nature, t.Apr
FROM Nature LEFT JOIN (SELECT NatureCountPerMonth.ComplaintNumber AS Apr, NatureCountPerMonth.Nature FROM NatureCountPerMonth WHERE Month = 4 AS t) ON Nature.Nature = t.Nature

But it doesn't work. How do I nest queries within queries? Is it a syntax problem or is this something that can't be done?

Thanks in advance for any help,Can you explain what you want to achieve?

Did you try this? Does this sql work?

SELECT Nature.Nature, t.Apr
FROM Nature,
(SELECT NatureCountPerMonth.ComplaintNumber AS Apr,
NatureCountPerMonth.Nature
FROM NatureCountPerMonth WHERE Month = 4) t
where Nature.Nature = t.Nature;|||Cheers,
That would be it... I figured you would change the name of a table the same way you change a field. This is what I needed.

Thanks,

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Query users in a Security Group with LDAP

I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:

SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')

Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?

Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql

query tuning

I have a query which is taking 23 sec to run, if i create a temporary table
for subset of resultset of the query and rewrite it using the temporary
table , which is taking only 4 sec. I cant mention my real query, but i
outline it here.
Original Query outline:
select pacct from (select pacct, qacct from tableA
where pid = '123456' and date > @.date) a
where qacct in (select qacct from TableB where groupid = 'asdfa' )
Modified query outline:
select pacct, qacct into #tp from tableA
where pid = '123456' and date > @.date
select pacct from #tp
where qacct in (select qacct from TableB where groupid = 'asdfa' )
TableA has 12 million recs , Table B has half a million recs.
I used inner join too, there is no improvment. From this can anyone guess
what is wrong , with optimiser or query.
Thanks,
Subbu.
Try this instead:
select pacct from tableA
where pid = '123456'
and date > @.date
and exists
(select *
from TableB
where groupid = 'asdfa'
and TableB.qacct = TableA.qacct)
If that doesn't work, post DDL for your tables, including all constraints
and indexes.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have a query which is taking 23 sec to run, if i create a temporary
table
> for subset of resultset of the query and rewrite it using the temporary
> table , which is taking only 4 sec. I cant mention my real query, but i
> outline it here.
> Original Query outline:
> select pacct from (select pacct, qacct from tableA
> where pid = '123456' and date > @.date) a
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> Modified query outline:
> select pacct, qacct into #tp from tableA
> where pid = '123456' and date > @.date
> select pacct from #tp
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> TableA has 12 million recs , Table B has half a million recs.
> I used inner join too, there is no improvment. From this can anyone guess
> what is wrong , with optimiser or query.
> Thanks,
> Subbu.
>
>
>
|||It is taking more than 150 sec and still going i stopped it.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Try this instead:
>
> select pacct from tableA
> where pid = '123456'
> and date > @.date
> and exists
> (select *
> from TableB
> where groupid = 'asdfa'
> and TableB.qacct = TableA.qacct)
>
> If that doesn't work, post DDL for your tables, including all constraints
> and indexes.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> table
guess
>
|||What are your indexes on the two tables?
-Sue
On Wed, 17 Nov 2004 10:23:06 -0600, "Subbaiahd"
<subbaiahd@.hotmail.com> wrote:

>It is taking more than 150 sec and still going i stopped it.
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...
>guess
>

query troubles

I used a query in VB with an access form as the front end to create a report...I have modified it to try to make it work in SQL Server...it is returning too many fields though currently....

Select dev.KinderNumber,dev.Client,dev.MFG,dev.FAMILY,dev .Intro_Date,dev.FIXTURE,dev.Dimensions_HWD,
dev.MFG_Price,dev.Item_Number,dp.picname from Development
as dev, DevelopmentPictures as dp
RIGHT JOIN Development ON dp.[KinderNum] = Development.[KinderNumber]
WHERE dp.[Revision_Letter]
IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = dp.KinderNum);

this returns about 57000 rows I usually have about 350-400..

also : IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = KinderNum);

if I take out the dp from dp.kindernum it only retunrs the row headings??

Please help!

thanks in advance,

JohnWhat does Development as dev join to? May be a cross join there...|||Since you are comparing the results from two different databases, VB and SQL Server, I'd suggest first running a quick check on the tables to see if you've got the same number of rows in both.|||jfouse

You didn't say what your query should return so this is a bit of a guess.

Select dev.KinderNumber
, dev.Client
, dev.MFG
, dev.FAMILY
, dev.Intro_Date
, dev.FIXTURE
, dev.Dimensions_HWD
, dev.MFG_Price
, dev.Item_Number
, dp.picname
from Development as dev
left join DevelopmentPictures as dp on dev.KinderNumber = dp.KinderNum
where dp.Revision_Letter = (select max(Revision_Letter)
from DevelopmentPictures
where DevelopmentPictures.KinderNum = dp.KinderNum)sql

Query to show life cycle revenue?

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table and a sales table joined by custno.

SELECT Customer.LastName, Sales.InvDate, Sales.AmtCharge
FROM Customer INNER JOIN
Sales ON Customer.CustNo = Sales.CustNo

The output I'd like is

CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount

Getting the first inv date seems straight forward

SELECT

Customer.CustNo,MIN(Sales.InvDate)AS FirstInvFROM CustomerINNERJOINSalesON Customer.CustNo= Sales.CustNoGROUPBY Customer.CustNo

However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.

Can anyone point me in the right direction?

Please give this one a try:

SELECT a.CustNo, a.LastName, b.AmtCharge AS FirstInvoiceAmount, a.LifeCycleAmount,(a.LifeCycleAmount-b.AmtCharge) AS LifeCycleAmountWithouttheFirst FROM (SELECT Customer.CustNo, Customer.LastName, SUM(Sales.AmtCharge) AS LifeCyleAmount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNoGROUPBY Customer.CustNo,Customer.LastName) AS a INNER JOIN ( SELECT Customer.CustNo,MIN(Sales.InvDate)AS FirstInv, Sales.AmtChargeFROM CustomerINNERJOINSalesON Customer.CustNo= Sales.CustNoGROUPBY Customer.CustNo,Sales.AmtCharge) AS b ON a.CustNo=b.CustNo

SELECT a.CustNo, a.LastName, b.AmtCharge AS FirstInvoiceAmount,
a.LifeCycleAmount, (a.LifeCycleAmount-b.AmtCharge)
AS LifeCycleAmountWithouttheFirst FROM
(SELECT Customer.CustNo, Customer.LastName,
SUM(Sales.AmtCharge) AS LifeCycleAmount FROM
Customer INNER JOIN Sales
ON Customer.CustNo = Sales.CustNo
GROUP BY Customer.CustNo, Customer.LastName)
AS a INNER JOIN (SELECT c.CustNo, c.minInvDate, d.AmtCharge FROM (SELECT Sales.CustNo, MIN(Sales.InvDate) AS minInvDate FROM Sales
GROUP BY Sales.CustNo) AS c INNER JOIN Sales d ON c.CustNo=d.CustNo AND c.minInvDate=d.InvDate) AS b ON a.CustNo=b.CustNo

But if a customer had two sales on the first day, you may need another column to get just the first one.

|||That worked perfectly for what I wanted thanks so much!

Query to return duplicate records

I have a table with a column varchar(50), say colA.
How can I create a sql query that returns all records with duplicate colA ?
For example:
colA colB
1 A
2 B
2 B
3 C
2 is the duplicate records for colA. How can I return those records ?
Thanks.SELECT ColA, count(*) FROM TableName
GROUP BY ColA
HAVING COUNT(*) > 1
HTH. Ryan
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>
SELECT T.cola, T.colb
FROM your_table AS T
JOIN
(SELECT cola
FROM your_table
GROUP BY cola
HAVING COUNT(*)>1) AS D
ON T.cola = D.cola ;
David Portas
SQL Server MVP
--|||Select colA,ColB
>From Sometable
Where colA in
(
Select colA
From SomeTable
Group by colA
Having count(*) >1
)
HTH, jens Suessmeyer.

Query to retreive user tables in sql db

Is there a way to create a query that will return all user tables inside a sql db

ThanxYou can SELECT * FROM a sysobjects table, where
you can filter the records by a 'U'ser type.

Regards,|||Microsoft always advises against querying the system tables directly. You could use the INFORMATION_SCHEMA.Tables view instead.

SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Catalog = 'myDatabase'

Terri|||what about a list of stored procedures using INFORMATION_SCHEMA?|||I am pretty sure you can get those out of INFORMATION_SCHEMA.ROUTINES. Check out the Index of Books Online.

Terri

Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

Query to linked server:Oracle; problems with ANSI_NULLS;ANSI_WARNINGS

When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)

Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query.

When I create a dynamic SQL statement then I can save this stored procedure
when I run the stored procedure this same error happens.

What do I have to do.

Arno de Jong, The Netherlands"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bq9lqp$qag$1@.reader11.wxs.nl...
> When I perform a query on a linked Oracle server in the Query analyser I
> have no
> prboblem' to perform this query.
> However, when I create this query in a stored procedure I get a
compilation
> error
> when saving this procedure. (Not when compiling; it has no errors)
> Server: Msg 7405, Level 16, State 1, Line 1
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query.
> When I create a dynamic SQL statement then I can save this stored
procedure
> when I run the stored procedure this same error happens.
> What do I have to do.
> Arno de Jong, The Netherlands

Have you tried putting SET ANSI_NULLS ON and SET ANSI_WARNINGS ON at the
start of your stored procedure, ie. in the procedure code itself? QA sets
these on automatically, but other connections may not.

Simon

Query to get all user tables with columns

Hi,

I tried to create a simple view as follows

CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

Combined with consistent naming conventions I will use this view to
easily find foreign keys; a la

SELECT *
FROM V_ALL_USERTABLE_COLUMNS
WHERE ColName LIKE ('%user_id')

There is something wrong with my view definition that I don't get
though; it doesn't return all the columns. I have a table with the
following definition

CREATE TABLE [dbo].[c_messages]
(
[cid] [int] IDENTITY (1, 1) NOT NULL ,
[touser_id] [int] NULL ,
[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fromuser_id] [int] NOT NULL ,
[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[message_read] [bit] NOT NULL ,
[logout] [bit] NULL
) ON [PRIMARY]
GO

The problem is that the select I used to define the view doesn't
return the touser_id column. I have sort of a sneaking suspicion that
the problem has to do with joining syscolumns.type to systypes.type,
but I don't know what to do instead (I'd really like to include the
type; it's useful if I ever changed the type of a primary key and want
to check that I also changed all the foreign keys).

Any help would be appreciated!Use the information schema rather than the system tables:

SELECT * FROM information_schema.columns

This format is much easier to use.

Your original query should work if you join on XTYPE rather than TYPE
but this is not recommended. In general you should avoid referencing
system tables directly.

--
David Portas
SQL Server MVP
--

Query to find who runs jobs

I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle

Not quite. You should check msdb for all things SQL Agent/job related.

Rather than querying the tables directly though, i'd recommend using the system sprocs:

Check out sp_update_job, sp_add_job, sp_help_job (plus associated sprocs) in Books Online.

HTH!

query to find the top 3 in each type

Hi

I need help in finding the query which will provide the following resultset from the below table..

Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)

Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)

Result Set:
product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50

i.e The result set gives the top 3 products in each product_type based on the no_of_units.

thanksselect * from product_stocks where product_id in (select top 3 product_id from product_stocks group by product_id )order by product_type,no_of_units desc|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework|||Hi harshal,

Thanks for providing the query.
It was very helpful and met my requirement.

thanks|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework

yeah I thought it would be a RFH..:mad: .

can u please enlighten me on the flaw part please...|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!

OHH!! :confused:
I m getting lazy day by day.. need to spend more time on the forums i guess..;)

thanks for pointing out..

harshal|||Hi harshal

I tested the query , but the result is not correct .
It provides the result set for the product_id 1 , 2 , 3 in each product_type and not the
top 3 in each product_type based on no_of_units

thanks|||arjun, try this --select one.product_type
, one.product_id
, one.no_of_units
from daTable as one
inner
join daTable as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units
group
by one.product_type
, one.product_id
, one.no_of_units
having count(*) <= 3 and be sure you can explain it when your teacher asks you how you got it|||Hi

Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
But what i need is the top 3 from each of the product_type .

thanks|||Even if order by is used in the sub query, it will give the top 3 product_id across all the product_typeno, not if it's a correlated subquery

But what i need is the top 3 from each of the product_typedid you try my query?|||Hi r937 ,

I tried your query. It returns the top 3 product_id from all product_types.
I need another help.
In the result set , the order of the result set varies for each product_type.

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

How to modify this so that the no_of_units for each product_type is in the descending order.

thanks.|||I tried your query. It returns the top 3 product_id from all product_types.you could not possibly have tried it

here is what it produces:A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90this is exactly what you asked for|||Hi r937,

I am getting the same result as you have posted.

The Result set is here :

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

In this result set , the no_of_units for product_type 'A' is in descending order,
but the no_of_units for product_type 'B' and 'C' is not in descending order.

What i seek is to get the no_of_units in descending order for each of the product_types.

thanks|||look up ORDER BY in your manual

:)|||look up ORDER BY in your manual

:)
Firstly you must look up ORDER By in manual as r937 said ,thats for your knowledge..
well,this time just try this to get your results..

select one.product_type
, one.product_id
, one.no_of_units
from product_stocks as one
inner
join product_stocks as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units

group
by one.product_type
, one.product_id
, one.no_of_units

having count(*) <= 3 order by one.product_type,one.no_of_units desc

Joydeep|||This is coming pretty close to baby-sitting. arjun, you need to become familiar with books online. If you can't find your answer there, or you don't understand something, then post a question.

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO

> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>