insert or replace: update

Showing posts with label update. Show all posts

Friday, March 30, 2012

INSERTED table performance

I have a table with one UPDATE trigger. When I execute a one row update
command
to the table, Graphical Query Plan reports very slow select from INSERTED
table (900ms).
However, if I check the same command using Profiler, everything goes quickly
(duration 0 ms). Why is that? Which one should I trust, profiler or query
plan?I trust Profiler more than the Graphical Query Plan. I have seen some quite
strange costs and percentages in the Graphical Query Plan, specially when
objects are involved that don't exist at the beginning of the query, like
the inserted and deleted tables, temporary tables and table variables
--
Jacco Schalkwijk
SQL Server MVP
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Pexi,
Something that surprise me when I first found out. Inserted and deleted do
not exist, but are virtual tables that are populated each time you query
them by scanning the transaction log to extract the before and after images.
This is why the suggestion is to fill temp tables #inserted and #deleted if
you need to make repeated use of these tables.
Regarding the difference in the timings, the best way to measure is to
create a test. Do a loop calling your UPDATE repeatedly and logging the
milliseconds in a table.
SET @.BeginTime = GetDate()
EXEC YourTestStatement
INSERT INTO TrackingTable Values(@.BeginTime, GetDate())
Afterward you can analyze the results (and publish an article).
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Thanks for the replies! You kind of confirm my thinking:
never trust the query plan - it just tells fairy tales sometimes :)
pexi
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>

inserted table

Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database AdministratorThe inserted table can indeed have > 1 row in it and you code should take
this into account. Likely, you don't need a cursor either.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
Is it ever possible for the inserted table to have more than one row in a
for update trigger? One of our devs recently put a cursor in his for update
trigger to loop over rows in the inserted table. However, from what I
understand, inserted should never have more than one row in it. I just
wanted to verify this before I removed it as I am working on optimizing it.
Brent Black
Onvia.com
Technical Lead/Database Administrator|||Don't use a cursor in a trigger, typically people do something like this:
update table set column = value where prinmarykey = (select primary key from
inserted)
If you will have multiple updates or inserts you would want to change it to
this
update table set column = value where prinmarykey IN (select primary key
from inserted)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Is it ever possible for the inserted table to have more than one row in a
> for update trigger? One of our devs recently put a cursor in his for
update
> trigger to loop over rows in the inserted table. However, from what I
> understand, inserted should never have more than one row in it. I just
> wanted to verify this before I removed it as I am working on optimizing
it.
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
>|||I've been able to do that in every case except where the ID value from the
cursor is being passed into a udf that returns a table.. For example:
insert into sometable (column1, column2)
select distinct @.CursorValue, pgr.ID
from someUDF(@.CursorValue) as pgr
I tried changing this to:
insert into sometable(column1, column2)
select distinct i.ID, pgr.ID
from someUDF(i.ID) as pgr,
inserted i
but that didn't work because it expects a single deterministic value to be
passed into the UDL.. It appears that was why the original dev chose to use
a cursor in the trigger to handle this in the first place. Any ideas on how
to do this without the cursor?
Thanks!
Brent Black
Onvia.com
Technical Lead/Database Administrator
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> Don't use a cursor in a trigger, typically people do something like this:
> update table set column = value where prinmarykey = (select primary key
from
> inserted)
> If you will have multiple updates or inserts you would want to change it
to
> this
> update table set column = value where prinmarykey IN (select primary key
> from inserted)
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Brent Black" <bblack@.onvia.com> wrote in message
> news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
a
> update
> it.
>|||What's the UDF look like?
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:ucGCsKN9DHA.1936@.TK2MSFTNGP12.phx.gbl...
> I've been able to do that in every case except where the ID value from the
> cursor is being passed into a udf that returns a table.. For example:
> insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
> I tried changing this to:
> insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to
be
> passed into the UDL.. It appears that was why the original dev chose to
use
> a cursor in the trigger to handle this in the first place. Any ideas on
how
> to do this without the cursor?
> Thanks!
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
this:
> from
> to
in
> a
just
optimizing
>|||Brent,
I wouldn't be surprised if in this case the UDF is something like
create function someUDF(
@.v somedatatype
) returns table ...
WHERE someColumn = @.v
...
If that's the case, then the trigger could probably be written by
joining the inserted
table with whatever the current UDF applies its WHERE clause to, or with
not much more work than that.
In other words, as Ray said, what does the UDF (and the trigger) look like?
SK
Brent Black wrote:

>I've been able to do that in every case except where the ID value from the
>cursor is being passed into a udf that returns a table.. For example:
>insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
>I tried changing this to:
>insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to be
>passed into the UDL.. It appears that was why the original dev chose to us
e
>a cursor in the trigger to handle this in the first place. Any ideas on ho
w
>to do this without the cursor?
>Thanks!
>Brent Black
>Onvia.com
>Technical Lead/Database Administrator
>"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
>news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>from
>
>to
>
>a
>
>
>|||Hi Brent,
Thank you for using the newsgroup.
Here is an example for your reference, you could run in your Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL ,
[test_column] varchar(2)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[author_fun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[author_fun]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function author_fun(@.state varchar(30))
returns table
as
return(select * from authors where @.state=authors.state
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
truncate table authorsx
insert into authorsx select *,1 from author_fun('CA')
select * from authorsx
go
drop table authorsx
So, I agree with Ray that if your the value returned by the UDF is match
the column you want to insert to or not.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Brent,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. For any questions, please feel free to post new
message here and I am glad to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

inserted table

The inserted table can indeed have => 1 row in it and you code should take this into account. Likely, you don't =need a cursor either.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brent Black" wrote in =message news:uhVRS4K9DHA.3404=@.TK2MSFTNGP09.phx.gbl...Is it ever possible for the inserted table to have more than one row in =afor update trigger? One of our devs recently put a cursor in his for updatetrigger to loop over rows in the inserted table. =However, from what Iunderstand, inserted should never have more than one row in =it. I justwanted to verify this before I removed it as I am working on optimizing it.Brent BlackOnvia.comTechnical =Lead/Database Administrator

--=_NextPart_000_01FB_01C3F484.E84A26E0--|||Don't use a cursor in a trigger, typically people do something like this:
update table set column = value where prinmarykey = (select primary key from
inserted)
If you will have multiple updates or inserts you would want to change it to
this
update table set column = value where prinmarykey IN (select primary key
from inserted)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Is it ever possible for the inserted table to have more than one row in a
> for update trigger? One of our devs recently put a cursor in his for
update
> trigger to loop over rows in the inserted table. However, from what I
> understand, inserted should never have more than one row in it. I just
> wanted to verify this before I removed it as I am working on optimizing
it.
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
>|||I've been able to do that in every case except where the ID value from the
cursor is being passed into a udf that returns a table.. For example:
insert into sometable (column1, column2)
select distinct @.CursorValue, pgr.ID
from someUDF(@.CursorValue) as pgr
I tried changing this to:
insert into sometable(column1, column2)
select distinct i.ID, pgr.ID
from someUDF(i.ID) as pgr,
inserted i
but that didn't work because it expects a single deterministic value to be
passed into the UDL.. It appears that was why the original dev chose to use
a cursor in the trigger to handle this in the first place. Any ideas on how
to do this without the cursor?
Thanks!
Brent Black
Onvia.com
Technical Lead/Database Administrator
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> Don't use a cursor in a trigger, typically people do something like this:
> update table set column = value where prinmarykey = (select primary key
from
> inserted)
> If you will have multiple updates or inserts you would want to change it
to
> this
> update table set column = value where prinmarykey IN (select primary key
> from inserted)
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Brent Black" <bblack@.onvia.com> wrote in message
> news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Is it ever possible for the inserted table to have more than one row in
a
> > for update trigger? One of our devs recently put a cursor in his for
> update
> > trigger to loop over rows in the inserted table. However, from what I
> > understand, inserted should never have more than one row in it. I just
> > wanted to verify this before I removed it as I am working on optimizing
> it.
> >
> > Brent Black
> > Onvia.com
> > Technical Lead/Database Administrator
> >
> >
>|||What's the UDF look like?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brent Black" <bblack@.onvia.com> wrote in message
news:ucGCsKN9DHA.1936@.TK2MSFTNGP12.phx.gbl...
> I've been able to do that in every case except where the ID value from the
> cursor is being passed into a udf that returns a table.. For example:
> insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
> I tried changing this to:
> insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to
be
> passed into the UDL.. It appears that was why the original dev chose to
use
> a cursor in the trigger to handle this in the first place. Any ideas on
how
> to do this without the cursor?
> Thanks!
> Brent Black
> Onvia.com
> Technical Lead/Database Administrator
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
> > Don't use a cursor in a trigger, typically people do something like
this:
> >
> > update table set column = value where prinmarykey = (select primary key
> from
> > inserted)
> >
> > If you will have multiple updates or inserts you would want to change it
> to
> > this
> >
> > update table set column = value where prinmarykey IN (select primary key
> > from inserted)
> >
> > HTH
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Brent Black" <bblack@.onvia.com> wrote in message
> > news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Is it ever possible for the inserted table to have more than one row
in
> a
> > > for update trigger? One of our devs recently put a cursor in his for
> > update
> > > trigger to loop over rows in the inserted table. However, from what I
> > > understand, inserted should never have more than one row in it. I
just
> > > wanted to verify this before I removed it as I am working on
optimizing
> > it.
> > >
> > > Brent Black
> > > Onvia.com
> > > Technical Lead/Database Administrator
> > >
> > >
> >
> >
>|||Brent,
I wouldn't be surprised if in this case the UDF is something like
create function someUDF(
@.v somedatatype
) returns table ...
WHERE someColumn = @.v
...
If that's the case, then the trigger could probably be written by
joining the inserted
table with whatever the current UDF applies its WHERE clause to, or with
not much more work than that.
In other words, as Ray said, what does the UDF (and the trigger) look like?
SK
Brent Black wrote:
>I've been able to do that in every case except where the ID value from the
>cursor is being passed into a udf that returns a table.. For example:
>insert into sometable (column1, column2)
> select distinct @.CursorValue, pgr.ID
> from someUDF(@.CursorValue) as pgr
>I tried changing this to:
>insert into sometable(column1, column2)
> select distinct i.ID, pgr.ID
> from someUDF(i.ID) as pgr,
> inserted i
> but that didn't work because it expects a single deterministic value to be
>passed into the UDL.. It appears that was why the original dev chose to use
>a cursor in the trigger to handle this in the first place. Any ideas on how
>to do this without the cursor?
>Thanks!
>Brent Black
>Onvia.com
>Technical Lead/Database Administrator
>"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
>news:OYCx3KL9DHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>>Don't use a cursor in a trigger, typically people do something like this:
>>update table set column = value where prinmarykey = (select primary key
>>
>from
>
>>inserted)
>>If you will have multiple updates or inserts you would want to change it
>>
>to
>
>>this
>>update table set column = value where prinmarykey IN (select primary key
>>from inserted)
>>HTH
>>--
>>Ray Higdon MCSE, MCDBA, CCNA
>>--
>>"Brent Black" <bblack@.onvia.com> wrote in message
>>news:uhVRS4K9DHA.3404@.TK2MSFTNGP09.phx.gbl...
>>
>>Is it ever possible for the inserted table to have more than one row in
>>
>a
>
>>for update trigger? One of our devs recently put a cursor in his for
>>
>>update
>>
>>trigger to loop over rows in the inserted table. However, from what I
>>understand, inserted should never have more than one row in it. I just
>>wanted to verify this before I removed it as I am working on optimizing
>>
>>it.
>>
>>Brent Black
>>Onvia.com
>>Technical Lead/Database Administrator
>>
>>
>>
>
>|||Hi Brent,
Thank you for using the newsgroup.
Here is an example for your reference, you could run in your Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL ,
[test_column] varchar(2)
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[author_fun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[author_fun]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function author_fun(@.state varchar(30))
returns table
as
return(select * from authors where @.state=authors.state
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
truncate table authorsx
insert into authorsx select *,1 from author_fun('CA')
select * from authorsx
go
drop table authorsx
So, I agree with Ray that if your the value returned by the UDF is match
the column you want to insert to or not.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Brent,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. For any questions, please feel free to post new
message here and I am glad to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Wednesday, March 28, 2012

Inserted & Deleted Tables!

Suppose a trigger gets fired when the following UPDATE query gets
executed:
---
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
---
Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?
Thanks,
ArpanHi
Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.
If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123800474.961292.259990@.g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
> ---
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ---
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
> Thanks,
> Arpan
>|||On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>---
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>---
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?
Hi Arpan,
Almost.
The exact correct way to put this is:
- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert/Updated SP from multiple tables

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.

As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:

CREATE PROCEDURE AddDrawStats
AS

Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int

set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing

INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)

CREATE PROCEDURE AddDrawStats2
AS

DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers

WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?

CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO

insert/update/delete without replication

Hi,
i have a peer to peer replication set up between 2 databases. There
was a parallel insert the databases are out of sync. i know the table
where the difference is. Is there any stored procedure with which i
can insert/update/delete without it being replicated?
You could use the SKIPERRORS flag, or you could modify the relevant
rteplication stored procedure on the subscriber to skip the change.
HTH,
Paul Ibison

Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Insert/Update with dynamic database name

My problem is as follows:

I need to transmit data between two databases on the same server, but I
have to use dynamic database names (they must be configurable). For
example I need to achive sth like that:

insert into [database1].[dbo].[table1]
(select columns from [table2])

when database1 is not known at implementation stage.

I know I can use EXEC @.t_sql_code, but I wonder if there is any other
way? (OPENROWSET doesn't seem to suit my needs)

Thanks in advance

Amfiamfi1 (amfi1@.poczta.fm) writes:
> I need to transmit data between two databases on the same server, but I
> have to use dynamic database names (they must be configurable). For
> example I need to achive sth like that:
> insert into [database1].[dbo].[table1]
> (select columns from [table2])
> when database1 is not known at implementation stage.
> I know I can use EXEC @.t_sql_code, but I wonder if there is any other
> way? (OPENROWSET doesn't seem to suit my needs)

Well, one thing you can do is to use stored procedures:

SELECT @.spname = @.srcdb + '.dbo.getmydata'
INSERT table1 (...)
EXEC @.spname

Using a dynamic SP name is not as messy as have all the code in dynamic
SQL.

Now, your example indicates that it is the target database that is
unknown to you, in which case my suggestion does not work.

A faint possibility is to set up a linked server that loops back to
your own server. The target database would then be in the connection
string. You could thus say:

INSERT MYSERVER..dbo.table (...)
SELECT...

and you would set up the linked server as you need it. But there is
an overhead for the loopback. And I must that I have not tested if
it actually works.

--
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

insert/update trigger

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@.work_ord_num = work_ord_num , @.work_ord_line_num = work_ord_line_num

Thanks for your help!tbl2 and tbl3 should be joined with inserted.

Insert/Update too slow

Hi,

I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

Insert/Update too slow

Hi,

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode], Email , [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

Insert/Update sql commands not saving to DB

Here is my problem :
I issue an insert statement to the db. While I am getting a return value of 1 (1 row was affected) the values never show up into the db when I open the DB in access. However, I can see the data when it does an SQL select inside the program. So for instance, I do an insert into ORDER values (1, 12, 5.99). (1 = item ID, 12 = quantity, 5.99 = price). I then do a select * from Order, and I get those values back. When I open the DB in access, in between doing the insert and the select, I dont see the values there either. It is like it is making a temporary copy of the DB in memory during the execution only. When I close the program and re-F5, the data is no longer there. Maybe we need some kind of commit transaction? What am I doing wrong? I am using VB.Net 2005/MS Access 2003. Here is the relevant code :
Private m_Connection As OleDbConnection
''' <summary>
''' Defines the path to the database.
''' </summary>
''' <remarks></remarks>
#If CONFIG = "Debug" Then
Public Const DB_PATH As String = "DBs\DB_Test.mdb"
#ElseIf CONFIG = "Release" Then
Public Const DB_PATH As String = "DBs\DB_Production.mdb"
#End If
Sub connect(ByVal p_path As String) Implements IPartyDBase.connect
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & p_path
m_Connection = New OleDbConnection(connect_string)
m_Connection.Open()
End Sub
Sub someSub(ByVal stock As StockClass)
Dim tempString
Dim command As OleDbCommand
command = m_Connection.CreateCommand
command.CommandType = CommandType.Text
tempString = "Insert into Stock VALUES (" & stock.ID & ", "
tempString = tempString & stock.Quantity & ", "
tempString = tempString & stock.Price & ")"
Dim tempInt as Integer
command.CommandText = tempString
tempInt = command.ExecuteNonQuery
If Not tempInt = 1 Then
Throw New Exception("Bad addStockToDB into Stock " & tempInt)
End If
End Sub
Sub anotherSub
p_dbase.connect(PartyDBaseAccess.DB_PATH)
p_dbase.someSub()
p_dbase.close()
End Sub
Edit : During execution, looking under bin/debug/DBs, there is a copy of the database that has all the transactions I did during execution... but the actual DB isnt being updated/copied over.
Ok, the problem was that the path was not implicit, and it was overwriting the DB in /bin/debug/DBs... so changing the DB attributes to never copy worked, and opening the file in /bin/debug/DBs instead of the place where it was copying from.

insert/update procs

Is it a good idea to consolidate both insert/update for a given table in one
stored proc?
I was thinking its always better to separate the two.
TIA.Trisha wrote:
> Is it a good idea to consolidate both insert/update for a given table
> in one stored proc?
> I was thinking its always better to separate the two.
> TIA.
I've seen it done both ways. With a combined proc, you need a way to
distinguish between an update and an insert. That's easy if you're
passing in a PK identity value (or NULL in the case of an insert). it's
easy to return the newly generated value using SCOPE_IDENTITY(). You
need to test the performance to see if a combined proc holds up without
recompiling. If you're using natural keys, then you'll need to test for
row existence or update and check @.@.rowcount. This is when I might turn
to separate procs.
David Gugick
Imceda Software
www.imceda.com

insert/update NULL instead of ''

Hi,
due to special reasons I have to ensure, that in insert and update
statements for varchar-columns (which allow NULL-values) the value ''
automatically becomes replaced by NULL before the records have been
inserted/updated. (This because I have to convert a large application from
another database -which automatically substituted '' by NULL- to SqlServer
2000 SP4).
My first thought was to find a setting in SqlServer server. But I didn't
found one. Is there any?
My second thought was to find a setting in the OLEDB-provider. But I didn't
found one. Is there any?
(I'm using OLEDB, but not ADO)
My third thought has been to define triggers for that case (my very first
ones). I did it as listed below. Is this the correct way? Or is there a
better way? Or perhaps a way with better performance?
create table test (primkey integer not null, testcol1 varchar(10), testcol2
varchar(10))
create trigger test_trigger_u on test instead of update
as
update test set primkey = i.primkey,
testcol1 = nullif(i.testcol1, ''),
testcol2 = nullif(i.testcol2, '')
from test t inner join inserted i
on t.primkey = i.primkey
create trigger test_trigger_i on test instead of insert
as
insert into test (primkey,
testcol1,
testcol)
select primkey,
nullif(testcol1, ''),
nullif(testcol2, '')
from inserted
Regards,
RainerRainer,
Why not just evaluate the incoming value (i.e., stored procedure parameter)
if the value IS NULL replace it with ''. You could optionally use a trigger
as well but the former would probably give better performance and should
probably be used unless you can't control the input method/application.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> due to special reasons I have to ensure, that in insert and update
> statements for varchar-columns (which allow NULL-values) the value ''
> automatically becomes replaced by NULL before the records have been
> inserted/updated. (This because I have to convert a large application from
> another database -which automatically substituted '' by NULL- to SqlServer
> 2000 SP4).
> My first thought was to find a setting in SqlServer server. But I didn't
> found one. Is there any?
> My second thought was to find a setting in the OLEDB-provider. But I
> didn't found one. Is there any?
> (I'm using OLEDB, but not ADO)
> My third thought has been to define triggers for that case (my very first
> ones). I did it as listed below. Is this the correct way? Or is there a
> better way? Or perhaps a way with better performance?
> create table test (primkey integer not null, testcol1 varchar(10),
> testcol2 varchar(10))
> create trigger test_trigger_u on test instead of update
> as
> update test set primkey = i.primkey,
> testcol1 = nullif(i.testcol1, ''),
> testcol2 = nullif(i.testcol2, '')
> from test t inner join inserted i
> on t.primkey = i.primkey
> create trigger test_trigger_i on test instead of insert
> as
> insert into test (primkey,
> testcol1,
> testcol)
> select primkey,
> nullif(testcol1, ''),
> nullif(testcol2, '')
> from inserted
> Regards,
> Rainer
>|||Jerry,
our application does not use stored procedures. It uses sql-statements to
select, insert, update and delete data. The application should run against
the previous database (Gupta SQLBase) and against MS SqlServer (depending on
the customer). This goal should be reached with as few source code
modifications as possible. I know, that I can change each affected
insert/update statement in the sourcecode and replace '' by NULL. But I'm
looking for a way to avoid doing this.
Do you think, the triggers are o.k.?
Do you know a better way?
regards,
Rainer
P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
"Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
> Rainer,
> Why not just evaluate the incoming value (i.e., stored procedure
> parameter) if the value IS NULL replace it with ''. You could optionally
> use a trigger as well but the former would probably give better
> performance and should probably be used unless you can't control the input
> method/application.
> HTH
> Jerry
> "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
> news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and use the triggers if changing th
e
procedures cannot be done (i.e. if there aren't any).
Or did I miss something?
ML|||Functionality wise...a trigger should work fine.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:uzfHiaC1FHA.904@.tk2msftngp13.phx.gbl...
> Jerry,
> our application does not use stored procedures. It uses sql-statements to
> select, insert, update and delete data. The application should run against
> the previous database (Gupta SQLBase) and against MS SqlServer (depending
> on the customer). This goal should be reached with as few source code
> modifications as possible. I know, that I can change each affected
> insert/update statement in the sourcecode and replace '' by NULL. But I'm
> looking for a way to avoid doing this.
> Do you think, the triggers are o.k.?
> Do you know a better way?
> regards,
> Rainer
> P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
> "Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
> news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and only use triggers if changing
the procedures is not an option (i.e. if - for some insane reason - there
aren't any).
Or am I missing something?
ML|||Ahh...ML...ok.
Something like:
CREATE TABLE #TEST
(ID INT NOT NULL,
VAL VARCHAR(10))
DECLARE @.VAL VARCHAR(10)
SET @.VAL = ''
INSERT #TEST(ID,VAL)
VALUES(1,NULLIF(@.VAL,''))
SELECT * FROM #TEST
--DROP TABLE #TEST
then?
HTH
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D577E511-2347-4A2B-9F26-FA578CFEF71B@.microsoft.com...
>I believe Rainer is actually looking for a way to *insert* null values
> instead of the supplied empty strings rather than trying to prevent null
> values from being inserted.
> NULLIF is the way to go. Yet, I'd suggest handling that in the insert
> procedures, rather than in the triggers, and only use triggers if changing
> the procedures is not an option (i.e. if - for some insane reason - there
> aren't any).
> Or am I missing something?
>
> ML|||As the modern German would say: Wonderbra!
ML|||Or as Cosmo would say: Wonderbro! ;-)
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D8DEB5B-C763-4B1F-A18E-2E87411B674A@.microsoft.com...
> As the modern German would say: Wonderbra!
>
> ML|||Hmm... which one? :)
http://en.wikipedia.org/wiki/Cosmo
MLsql

Insert/Update into a SQL table

update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.c o.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegro ups.com...
>
|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen
|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen
|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegr oups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

Insert/Update into a SQL table

I have the following keys in Consumption:
- Plant
- Material
- Month
- Year
The above are the primary keys in the table and the following are
non-key fields:
- Quantity
- Amount
I have data stored in this table currently but many times I get feeds
which are stored in the table:
Consumption_staging which as the following fields:
Plant
Material
Month
Year
Quantity
Amount
even in the staging table - plant, material, month,year are the keys.
Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:
If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.
Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.
Thanks
Karenupdate Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL
<karenmiddleol@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> I have the following keys in Consumption:
> - Plant
> - Material
> - Month
> - Year
> The above are the primary keys in the table and the following are
> non-key fields:
> - Quantity
> - Amount
> I have data stored in this table currently but many times I get feeds
> which are stored in the table:
> Consumption_staging which as the following fields:
> Plant
> Material
> Month
> Year
> Quantity
> Amount
> even in the staging table - plant, material, month,year are the keys.
> Now I want to update data from the Consumption_staging to the
> Consumption table on the following criteria:
> If for the same Key fields as in Consumption_Staging if a record is
> already present in Consumption table then the record in Consumption
> must be updated with the non-key fields else the record from
> Consumption_staging must be inserted into the Consumption table.
> Greatly appreciate if you could kindly share the SQL code for this
> problem I want to just do it possibly just in SQL.
> Thanks
> Karen
>|||Ooops, I booboo'd on the update :)
update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
>|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegroups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

Insert/Update into a SQL table

I have the following keys in Consumption:
- Plant
- Material
- Month
- Year
The above are the primary keys in the table and the following are
non-key fields:
- Quantity
- Amount
I have data stored in this table currently but many times I get feeds
which are stored in the table:
Consumption_staging which as the following fields:
Plant
Material
Month
Year
Quantity
Amount
even in the staging table - plant, material, month,year are the keys.
Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:
If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.
Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.
Thanks
Karenupdate Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL
<karenmiddleol@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> I have the following keys in Consumption:
> - Plant
> - Material
> - Month
> - Year
> The above are the primary keys in the table and the following are
> non-key fields:
> - Quantity
> - Amount
> I have data stored in this table currently but many times I get feeds
> which are stored in the table:
> Consumption_staging which as the following fields:
> Plant
> Material
> Month
> Year
> Quantity
> Amount
> even in the staging table - plant, material, month,year are the keys.
> Now I want to update data from the Consumption_staging to the
> Consumption table on the following criteria:
> If for the same Key fields as in Consumption_Staging if a record is
> already present in Consumption table then the record in Consumption
> must be updated with the non-key fields else the record from
> Consumption_staging must be inserted into the Consumption table.
> Greatly appreciate if you could kindly share the SQL code for this
> problem I want to just do it possibly just in SQL.
> Thanks
> Karen
>|||Ooops, I booboo'd on the update :)
update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> update Consumption
> set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
> from Consumption_staging CS
> inner join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> insert into Consumption ( p , ma, mo, yr, qu, am )
> Select p , ma, mo, yr, qu, am from Consumption_staging CS
> left outer join Consumption C
> ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
> WHERE C.p IS NULL
>
>
> <karenmiddleol@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegroups.com...
> > I have the following keys in Consumption:
> >
> > - Plant
> > - Material
> > - Month
> > - Year
> >
> > The above are the primary keys in the table and the following are
> > non-key fields:
> >
> > - Quantity
> > - Amount
> >
> > I have data stored in this table currently but many times I get feeds
> > which are stored in the table:
> >
> > Consumption_staging which as the following fields:
> >
> > Plant
> > Material
> > Month
> > Year
> > Quantity
> > Amount
> >
> > even in the staging table - plant, material, month,year are the keys.
> >
> > Now I want to update data from the Consumption_staging to the
> > Consumption table on the following criteria:
> >
> > If for the same Key fields as in Consumption_Staging if a record is
> > already present in Consumption table then the record in Consumption
> > must be updated with the non-key fields else the record from
> > Consumption_staging must be inserted into the Consumption table.
> >
> > Greatly appreciate if you could kindly share the SQL code for this
> > problem I want to just do it possibly just in SQL.
> >
> > Thanks
> > Karen
> >
>|||Many thanks the update query works fine but the Insert comes back with
this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.
But the insert never works please appreciate
Thanks
Karen|||The insert gives more errors as follows:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.
Thanks
Karen|||It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.
Best solution is to ask whoever sent you the file to fix the data export.
Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1
This will give you records with missing data.
SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL
HTH
<karenmiddleol@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegroups.com...
> The insert gives more errors as follows:
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
> duplicate key in object 'Consumption'.
> The statement has been terminated.
> Thanks
> Karen
>

INSERT/UPDATE Date Format problem...

Hi,
This is a problem that everybody knows I guess: When you INSERT our UPDATE a
date in an Sql Server half of the time your date changes. For example: you
want to input two dates: 13th of May (13/05/2003) and 12th of May
(12/05/2003). The first one will always be in the database as "13/05/2003"
because the database knows 13 can't hbe a month. But for the second one you
need to get lucky: there's always a big chance (depending on the regional
settings?) that he will put it in the database as "05/12/2003" and thinks it
is 5th of December instead of 12th of May.
I used to have this problem in VB6, and now again I have it in VB.NET. In
VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
dd/MM/yyyy.
But still I think this isn't a 'nice' way. There should be a way which is
independed of regional settigns etc, and doens't force you to use 'trics'.
Does anybody here know how to do this?
Thanks a lot in advance!
PieterAny of the following formats are "safe" - they work independently of the
server's regional settings
'20031231'
'2003-12-31T17:59:00'
'2003-12-31T17:59:00.000'
Example:
UPDATE Sometable
SET datecol = '20031231'
WHERE ...
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks! I will try this!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Ws-dnW0JbYJ2fUiiRVn-jg@.giganews.com...
> Any of the following formats are "safe" - they work independently of the
> server's regional settings
> '20031231'
> '2003-12-31T17:59:00'
> '2003-12-31T17:59:00.000'
> Example:
> UPDATE Sometable
> SET datecol = '20031231'
> WHERE ...
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||<%
'This function recieves a date from text string in format dd/mm/yy or
dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a
datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a
CCYY year
'--
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'--
'pass a date as dd/mm/yy
Function convDate(theDate)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2)) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:3fd5df92$0$289$ba620e4c@.reader5.news.skynet.be...
> Hi,
> This is a problem that everybody knows I guess: When you INSERT our UPDATE
a
> date in an Sql Server half of the time your date changes. For example: you
> want to input two dates: 13th of May (13/05/2003) and 12th of May
> (12/05/2003). The first one will always be in the database as "13/05/2003"
> because the database knows 13 can't hbe a month. But for the second one
you
> need to get lucky: there's always a big chance (depending on the regional
> settings?) that he will put it in the database as "05/12/2003" and thinks
it
> is 5th of December instead of 12th of May.
> I used to have this problem in VB6, and now again I have it in VB.NET. In
> VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
> dd/MM/yyyy.
> But still I think this isn't a 'nice' way. There should be a way which is
> independed of regional settigns etc, and doens't force you to use 'trics'.
> Does anybody here know how to do this?
> Thanks a lot in advance!
> Pieter
>

Friday, March 30, 2012

INSERTED table performance

inserted table

inserted table

Wednesday, March 28, 2012

Inserted & Deleted Tables!

Insert/Updated SP from multiple tables

insert/update/delete without replication

Insert/update/delete Transaction

Insert/Update with dynamic database name

insert/update trigger

Insert/Update too slow

Insert/Update too slow

insert/update timestamp in a SQL server 2000 db programatically

Insert/Update statements or Stored Procs

Insert/Update sql commands not saving to DB

insert/update procs

insert/update NULL instead of ''

Insert/Update into a SQL table

Insert/Update into a SQL table

Insert/Update into a SQL table

INSERT/UPDATE Date Format problem...

insert or replace

Blog Archive

About Me