insert with condition question

I have tables of Vacations and VacationItems. Each Vacation has an owner and
is made up of one or more Vacation items. Vacation items have a startDate an
d
an endDate. A Vacation can only be inserted if none of its Vacation items
clash with any other Vacation items in Vacations owned by the user i.e a
user cannot book a Vacation on days he has already booked another Vacation o
n
I assume that I would wrap up each of the inserts in a transaction and if
any failed I would roll back the transaction.
What I want to know, at the moment, is how I would do an insert for a
Vacation item that would fail if the above condition had not been met
e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1,
01/01/05, 01/05/05)
WHERE
……..
Script
CREATE TABLE [dbo].[Vacation] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[VacationItem] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[VacationID] [int] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Vacation] ADD
CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VacationItem] ADD
CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VacationItem] ADD
CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
(
[VacationID]
) REFERENCES [dbo].[Vacation] (
[ID]
)
GOYou could use and INSTEAD OF INSERT trigger to check if the date range was
valid before performing the inserts.
--
Adam J Warne, MCDBA
"John" wrote:

> I have tables of Vacations and VacationItems. Each Vacation has an owner a
nd
> is made up of one or more Vacation items. Vacation items have a startDate
and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1
,
> 01/01/05, 01/05/05)
> WHERE
> ……..
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||Hi John
1. U could place a check for existence/validity just before doing an insert
into vacation item table & take suitable action ([insert and continue] or
[rollback and return error] or [bypass]).
2. U Can also think using IO triggers.. but again the above chk will be done
here
I would prefer the 1st suggestion
Rgds, Rakesh
"John" wrote:

> I have tables of Vacations and VacationItems. Each Vacation has an owner a
nd
> is made up of one or more Vacation items. Vacation items have a startDate
and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1
,
> 01/01/05, 01/05/05)
> WHERE
> ……..
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||Did you try
Insert into VacationItems
Select ....
from...
where....
Thanks,
Pradeep Kutty
"John" <John@.discussions.microsoft.com> wrote in message
news:BD4AA01F-E57C-4F9F-AD8F-70B91B8E775D@.microsoft.com...
>I have tables of Vacations and VacationItems. Each Vacation has an owner
>and
> is made up of one or more Vacation items. Vacation items have a startDate
> and
> an endDate. A Vacation can only be inserted if none of its Vacation items
> clash with any other Vacation items in Vacations owned by the user i.e a
> user cannot book a Vacation on days he has already booked another Vacation
> on
> I assume that I would wrap up each of the inserts in a transaction and if
> any failed I would roll back the transaction.
> What I want to know, at the moment, is how I would do an insert for a
> Vacation item that would fail if the above condition had not been met
>
> e.g. Insert into VacationItems (VacationID, startDate, endDate) values (1,
> 01/01/05, 01/05/05)
> WHERE
> ...
>
> Script
> CREATE TABLE [dbo].[Vacation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[VacationItem] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [VacationID] [int] NOT NULL ,
> [StartDate] [smalldatetime] NOT NULL ,
> [EndDate] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Vacation] ADD
> CONSTRAINT [PK_Vacations] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [PK_VacationItems] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[VacationItem] ADD
> CONSTRAINT [FK_VacationItems_Vacations] FOREIGN KEY
> (
> [VacationID]
> ) REFERENCES [dbo].[Vacation] (
> [ID]
> )
> GO
>|||First, fix the schema design. Both tables lack alternate keys. IDENTITY
should never be the only key of a table. Vacation is redundant because
it contains zero information except userid.
You can prevent overlapping dates by using a combination of constraints
and a trigger:
CREATE TABLE vacations (
userid INTEGER NOT NULL /* REFERENCES Users (userid) */,
startdate SMALLDATETIME NOT NULL ,
enddate SMALLDATETIME NOT NULL,
CONSTRAINT pk_vacation
PRIMARY KEY (userid, startdate),
CONSTRAINT ck_vacations
CHECK (startdate <= enddate)
)
GO
CREATE TRIGGER trg_vacation ON vacations
FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM vacations AS V
JOIN inserted AS I
ON I.startdate <= V.enddate
AND I.enddate >= V.startdate
AND I.startdate <> V.startdate
AND I.userid = V.userid)
BEGIN
RAISERROR('Violation of overlap constraint', 16, 1)
ROLLBACK TRAN
END
GO
David Portas
SQL Server MVP
--|||Preventing time overlaps requires user intervention => values should be
resolved in the application *before* any insert/update is attempted.
ML

Insert with condition

Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx
you can resort to dynamic SQL, e.g.

EXEC('insert into table (' + @.columnName + ') VALUES (' + @.value + ') WHERE ID =' + @.id)

HTH,|||Thanks for your reply. But that was my mistake that i thought Insert is what i need. I should use Update instead.Anyway it is the same story. I am using that in an c# application(web service).
I have this in my code:
objConnect = new SqlConnection(connectionString);
string queryString = "upadte MyTable set "+col+"= "+data+" where DeviceID = "+id+";";
try
{
objConnect.Open();
SqlCommand objCommand = new SqlCommand(queryString, objConnect);
objCommand.ExecuteReader();
}
...

It doesn't work, and because it is inside a web method i get the SoapException ..
I also tried with stored procedure,like this:
ALTER PROCEDURE [dbo].[UpdateData]
@.devID varchar(30),
@.dataCol varchar(30),
@.dataVal varchar(30)
AS
BEGIN
SET NOCOUNT ON;

update DataTransmission
set @.dataCol= @.dataVal
where DeviceID = @.devID
END
and it also doen't work when i do : exec UpdateData 'id','user','me'
Do you know what is going wrong?
|||

I am not sure about the cause of failure in your C# application, but the proc UpdateData will not give you desired result because " set @.dataCol= @.dataVal" will set the variable @.dataCol to have the value of the @.dataVal. The column is not touched at all. You can use dynamic sql inside the proc to achieve what you desire.

Thanks

|||I tried to use dynamic sql in stored procedure like this:
create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end
set col2 = case @.col when 'col2' then @.value end
where id = @.id

End

do you think it is right way?
|||that's just fine... but typing will be tedious if you have many columns in your table. |||But sql server has another idea it says there is syntax error (near '=').do you know what is wrong with it? when i check CASE in books, the syntax is right..So i don't know the problem.
|||Remove your second SET statement

create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end,
col2 = case @.col when 'col2' then @.value end
where id = @.id

End

HTH,|||Thanks alot. finally works
|||no prob... glad to be of help

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
1;Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blog...e
s.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.&
#91;Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blog...
es.html
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewritin
g
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CAS
E
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.comsql

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
End
Could you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.com

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via http://www.sqlmonster.com