Using EXISTS

Jul 23, 2005

I need to insert records into the table parSalesDetailModifier from
OLDparSalesDetailModifier where (1) those records DO NOT exit in
parSalesDetailModifier and (2) those records have a parent record in
parSalesDetail.

When I run the below query I get the error message that I am violating
the Primary Key Constraint for parSalesDetailModifier. In other words,
it's trying to insert a record that does exist.

Also posted below are create and insert startements for thte tables.

If someone would be kind enough to show me what I am doing wrong, I'd
really appreciate it.

Thanks,
Jennifer

-------------------------------
- STORED PROCEDURE
-------------------------------
CREATE Proc LoadModifier2

@S datetime,
@E datetime

AS

INSERT INTO ParSalesDetailModifier
(
parSalesDetailModifierID,
parSalesHdrID,
parSalesDetailID,
ModifierType,
POSModifier,
Condiment,
CondimentPrice,
UnitNumber,
BusinessDay
)

SELECT
OLD.parSalesDetailModifierID,
OLD.parSalesHdrID,
OLD.parSalesDetailID,
OLD.ModifierType,
OLD.POSModifier,
OLD.Condiment,
OLD.CondimentPrice,
OLD.UnitNumber,
OLD.BusinessDay

FROM OldParSalesDetailModifier OLD

WHERE

EXISTS
( SELECT DET.parSalesHdrID,
DET.parSalesDetailID,
DET.UnitNumber,
DET.BusinessDay
FROM ParSalesDetail DET
WHERE OLD.parSalesHdrID = DET.parSalesHdrIDAND
OLD.parSalesDetailID = DET.parSalesDetailIDAND
OLD.UnitNumber = DET.UnitNumberAND
OLD.BusinessDay = DET.BusinessDay
)

AND

NOT EXISTS
( SELECT NEW.parSalesHdrID,
NEW.parSalesDetailID,
NEW.parSalesDetailModifierID,
NEW.UnitNumber,
NEW.BusinessDay
FROM ParSalesDetailModifier NEW
WHERE OLD.parSalesHdrID = NEW.parSalesHdrIDAND
OLD.parSalesDetailID = NEW.parSalesDetailIDAND
OLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDAND
OLD.UnitNumber = NEW.UnitNumberAND
OLD.BusinessDay = NEW.BusinessDay
)

AND OLD.BusinessDay between @S and @E

-------------------------------
- END STORED PROCEDURE
-------------------------------


-------------------------------
- CREATE TABLES
-------------------------------

CREATE TABLE [parSalesDetailModifier] (
[ParSalesDetailModifierID] [int] NOT NULL ,
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[ModifierTYPE] [int] NULL ,
[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CondimentPrice] [money] NOT NULL ,
[UnitNumber] [int] NOT NULL ,
[BusinessDay] [datetime] NOT NULL ,
CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID],
[ParSalesDetailModifierID]
) WITH FILLFACTOR = 70 ON [PRIMARY] ,
CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
) REFERENCES [parSalesDetail] (
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
)
) ON [PRIMARY]
GO

CREATE TABLE [OLDparSalesDetailModifier] (
[ParSalesDetailModifierID] [int] NOT NULL ,
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[ModifierTYPE] [int] NULL ,
[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CondimentPrice] [money] NOT NULL ,
[UnitNumber] [int] NOT NULL ,
[BusinessDay] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [parSalesDetail] (
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[Before] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[After] [int] NOT NULL ,
[Promo] [money] NOT NULL ,
[PromoBefore] [money] NOT NULL ,
[ItemPrice] [money] NOT NULL ,
[PromoAfter] [money] NOT NULL ,
[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitNumber] [int] NOT NULL ,
[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmountTenderTime] [datetime] NULL ,
[BusinessDay] [datetime] NOT NULL ,
CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID],
[parSalesDetailID]
) WITH FILLFACTOR = 70 ON [PRIMARY] ,
CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY
(
[BusinessDay],
[UnitNumber],
[parSalesHdrID]
) REFERENCES [parSalesHdr] (
[BusinessDay],
[UnitNumber],
[parSalesHdrID]
)
) ON [PRIMARY]
GO

-------------------------------
- END CREATE TABLES
-------------------------------

-------------------------------
- INSERT INTO TABLES
-------------------------------

insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')

insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NO
ONIN','NO ONIN',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADD
G-ON','ADD G-ON',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUB
MAYO','SUB MAYO',.0000,2,'2003-12-01')
insert into oldparSalesDetailmodifier values
(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')

insert into parSalesDetail
values(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-01
10:00:02.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-01
10:09:44.000','2003-12-01')
insert into parSalesDetail
values(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-01
10:09:44.000','2003-12-01')
insert into parSalesDetail
values(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-01
10:00:44.000','2003-12-01')


-------------------------------
- END INSERT INTO TABLES
-------------------------------

View 7 Replies


ADVERTISEMENT

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

IF NOT EXISTS

Jul 7, 2006

Hello,
I am trying to create a table if one with the same name does not exists.  My code is:
Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"
sqlConnection.Open()
Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')"
Dim sqlCommand As New SqlCommand(newTable, sqlConnection)
If sqlExists = True Then
sqlCommand.Cancel()
Else
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End If
I keep getting a "Input String was incorrect format" for sqlExists?  I am new to Transact-SQL statements, any help would be appreciated.
Thanks Matt

View 1 Replies View Related

Where Not Exists---help

May 27, 2007

ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'.
My other table Instructors has 3 fields InstructorName, LastName, FirstName.
I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows..
Insert into Instructors(InstructorName, LastName, FirstName)
(Select Distinct
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1),
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName,
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation
where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName)
AND Instructor is not null)
 
Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes).  I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.
 

View 3 Replies View Related

NOT EXISTS Vs NOT IN

Feb 7, 2008

HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)  

View 2 Replies View Related

If Not Exists

Oct 12, 2001

Hi,

I have a question on the if not exists. Can you do something like this? If request not exists in table one then insert values into table two?

Thank you in advance,
Anne

View 3 Replies View Related

If Exists..

Jul 15, 2004

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.

View 2 Replies View Related

Help With EXISTS

Jun 15, 2007

hi guys! is there anyway to retrieve the row from the result of EXISTS function aside from using the code below?


if EXISTS (select * from rcps_useraccount where User_login = 'daimous')
BEGIN
select * from rcps_useraccount where User_login = 'daimous'
END

View 3 Replies View Related

Exists

May 16, 2008

Im having a problem with the following can anyone spot how i can fix it? I dont think it likes the begin statement but without it, it has a declare issue.




IF EXISTS (SELECT 1
FROM snapevent.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='FastEp_Snap_OD_Acc' + preports.dbo.f_filedate(getdate())
begin
declare @CMD nvarchar(300)
Set @CMD = 'drop table Snap_OD_Acc_' + preports.dbo.f_filedate(getdate())
--print @CMD
exec (@CMD)
end

View 1 Replies View Related

If Exists - SP

Jun 20, 2008

Hi

Please help me

I have to create a SP.

The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)

UserID AcctID Level1 level2
test testee N Y

the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.


After checking all the strings ,
it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

View 1 Replies View Related

Where Exists

Feb 18, 2006

I'm trying to perform an insert query on a table, but I also want to check to see if the record exists already in the table. It should be fairly simple, but I'm having a time of it. Should be something like:

select * from users u
inner join miamiherald m
on u.emailaddress = m.advertiseremail
where not u.emailaddress not exists <<< (???)

If it does exist, I then want to retrieve two columns from it. HELP!!

View 1 Replies View Related

IF EXISTS

May 28, 2007

Hi,

The following works in SQL 2005 but NOT SQL 2005 Compact Edition:

IF EXISTS (SELECT ID FROM Court2 WHERE BookingDate = '2007-05-28')
UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')
ELSE
INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52)

In CE I get the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I can't find where the problem is - can someone help.

Thanks,

View 5 Replies View Related

Which One Is Better In Or Exists

Dec 6, 2007

Dear all,
i am trying to improve the performance of stored procedures and functions in that in key word is there i have to replace with exists.which one will give better performance.


Thanks&Regards,

Msrs

View 2 Replies View Related

From In To Exists

Dec 6, 2007

Dear all,
i'm trying to replace in with exists...
i've changed the query but still i'm getting error.
please let me know where i'm missing

DELETE FROM table25 WHERE Col6='SET' AND Col1 IN (SELECT s.Col1 FROM Table24 s
WHERE Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT s.Col1 FROM Table24 s
WHERE r.col1=s.col2 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

thank you very much


Vinod
Even you learn 1%, Learn it with 100% confidence.

View 9 Replies View Related

Need Help With IF And EXISTS

Jan 15, 2008

I'm trying to search my table for a url, and if it in the table, increment the number of hits of the url. Here's my code:

tblLinks
-----------------------
URL | NumOfHits
-----------------------



ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF EXISTS(SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL)
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN


I've also tried:


ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF (SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL) IS NOT NULL
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN


but that didn't work either.

View 7 Replies View Related

IF EXISTS

Mar 20, 2008

There's an error in this SQL statement somewhere, but I can't seem to find it. Here's what I'm trying to do:

I have a list of states, and each state is assigned an ID
For each state, I have some data
Given a state name, find the state's ID
If the state ID exists, output the data for that state
If the state ID is not in the database, output the data for all states

Here's my tables:

--------------------------
States
--------------------------
ID | Name
--------------------------


--------------------------
Data
--------------------------
StateID | StateData
--------------------------



Here's my SQL:

ALTER PROCEDURE sproc_GetStateData
(
@State varchar
)
AS
DECLARE @StateID int

IF EXISTS(SELECT @StateID = ID FROM States WHERE Name = @State)
BEGIN
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
RETURN

View 6 Replies View Related

Help With Exists

Jul 23, 2005

Hi,I need some help doing a simple query. Here is what I haveSELECT Category_Id, Category_nameFROM AG_Category WHERE Auth_Logic_Id = 244And Category_ID(I want category_id to not have a '.' in it. Basically returningeverything above but checking also the category_id so it doesn't returnone where category_id have a . in it)Thanks:D

View 1 Replies View Related

IF EXISTS Help

Oct 31, 2007

Hi,
I'm having problems with this T-SQL:
Sorry if this is obvious, I'm very new to this...

Just trying to return the ID if the row exists and if not just return 0.
What would I need to turn this into a stored procedure too?





Code Block

declare @Return int

if exists(select id from Computer where Computer_name = 'MYCOMPUTER')
begin
select @Return = id
PRINT @Return
end
else
begin
select @Return = 0
PRINT @Return
end


I'm getting this error....

Msg 207, Level 16, State 1, Line 5Invalid column name 'id'.Msg 207, Level 16, State 1, Line 10Invalid column name 'id'.

View 5 Replies View Related

NOT IN, NOT EXISTS

Mar 10, 2008



I'm running into a problem on my SQL2000 server. I thought all 3 of the following queries SHOULD return the same results, but the query using the NOT IN does not return any records where the other 2 queries DO.

Is my understanding of NOT IN wrong, or is there something corrupt in my DB?

SELECT *
FROM [person]
WHERE personid NOT IN (SELECT personid FROM [personOrganization])

SELECT *
FROM [person]
LEFT OUTER JOIN [personOrganization]
ON [Person].[personId] = [personOrganization].[personId]
WHERE [personOrganization].personid IS NULL

SELECT *
FROM person
WHERE NOT EXISTS(SELECT 1 FROM [personOrganization] WHERE [personOrganization].personid=person.personid)


View 11 Replies View Related

IF NOT EXISTS

Mar 25, 2008

Hi all,

I'm trying to run "alter table" to add a new column to the tables but the problem is some of the tables have the column and some don't.

Is there a way to use if not exists to alter the tables with no column only??

thanks,

View 7 Replies View Related

EXISTS

Aug 30, 2006

Well, finally I lost one day to find a solution for a simple problem. I
wanted to derive two tables... that seemed to be simple... but it isn't.



My last question for today:



Why does my sql query return the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS



The Query:



SELECT
Configurations.AlarmId, Configurations.Mode, Configurations.Activated,
Configurations.Empty, Configurations.UserName, Configurations.datetime,

(SELECT ConfigurationId, Setpoint1, Alarm

FROM
dbo.ExtendedValues(Configurations.ConfigurationId) AS ExtendedValues)
AS Expr1
FROM Configurations INNER JOIN

Controllers ON Configurations.ConfigurationId =
Controllers.ActiveConfig INNER JOIN

Tanques ON Controllers.ControllerId = Tanques.ControllerId
WHERE (Tanques.TanqueId = 1)



Where do I have to enter EXISTS?



Thanks for every hint!!



P.D.: The ExtendedValues Function:



CREATE FUNCTION dbo.ExtendedValues
    (
        @ConfigurationId int = -1
    )
RETURNS TABLE
AS
    RETURN SELECT *
FROM ConfigurationsTanques
WHERE ConfigurationsTanques.ConfigurationId = @ConfigurationId

View 1 Replies View Related

Database With The Same Name Exists

Apr 12, 2007

Hi guys,What is this errorAn attempt to attach an auto-named database for file C:inetpubwwwrootMediaApp_DataLibrary.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

View 3 Replies View Related

How To See If Value Exists In Table

May 7, 2007

I have a textbox1 control where a zip code is entered.  What is the most efficient way in C# code behind to see if  that zip code exists in tblZipCode?  I only need to know if it is there, do not need to know how many occurances.

View 7 Replies View Related

If Exists Problem

Nov 25, 2007

I want to enter a website address into a TextBox and on AutoPostBack check to see if the entry is LIKE any in a SQL 2000 database tblLinkAddresses.  If it is not - add it to the table and if it is display the LIKE matches.
Where do I start?

View 2 Replies View Related

Cannot Add An Entity That Already Exists.

Dec 30, 2007

 Hello,         Why do I receive this error "Cannot add an entity that already exists."         I'm trying to add three data at one call using LINQ, after getting the error above then refreshing the page, only one data is inserted....    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim db As personalDataContext = New personalDataContext        Dim p As New personal        For i = 0 To 3 - 1            p.name = "Mick"            p.number = "01213"            p.picture = "image/image.jpg"            db.personals.InsertOnSubmit(p)            db.SubmitChanges()        Next    End Sub...  cheers,imperialx 

View 6 Replies View Related

IF NOT EXISTS StoredProcedure

May 14, 2008

Visual Studio 2008 Code VB
 I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS .  With the below code I can update the table, however, you can not add additional rows to the table.
Could someone tell me what is wrong, or how to fix it?
 
Thanks! losssoc  ALTER PROCEDURE dbo.CaseDataInsert
 
@ReportType varchar(50),@CreatedBy varchar(50),
@OpenDate smalldatetime,@Territory varchar(10),
@Region varchar(10),@StoreNumber varchar(10),
@StoreAddress varchar(200),@TiplineID varchar(50),
@Status varchar(50),@CaseType varchar(200),
@Offense varchar(200)
 
AS
BEGIN
IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID,
@Status,@CaseType,@Offense)
 
END
 

View 12 Replies View Related

How To Know If A Record Exists

May 21, 2006

Hello,I have a TextBox and an Insert Button, it works like this: protected void Button1_Click(object sender, EventArgs e) { String strConn=SqlDataSource1.ConnectionString; SqlCommand cmd = new SqlCommand("INSERT INTO My_Table_1 (Column_1) VALUES ("+TextBox1.Text+")", new SqlConnection(strConn)); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } But, what code i must write for checking, if the value i am trying to insert already exists ??I mean something like this:if (TextBox1.Text does not Exists on any Record in My_Table_1) then        Insert itelse        Show Message : "Already exists a record with this value"Thank you SO MUCH, guys,Carlos.

View 7 Replies View Related

If Exists Myprocedure?

Nov 1, 2001

Please for me to help... I guess I need a way to check if the up_UpdateHFOTable procedure exists.

I'm trying to enable checking for completion of a set of tasks the first of which is the creation of a table via a user procedure up_UpdateHFOTable.

If that fails I want to exit my master_proc and return a message to that effect.

My master_proc has the following code...

exec @int_stat = up_UpdateHFOTable

If @int_stat <> 0
Begin
set @result = @error
set @error_msg =
@error_msg + ' The HFO table could not be created '
set @continue = 99
End


Upon creation of my master_proc I'm getting...

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'up_UpdateHFOTable'. The stored procedure will still be created.

Then when I run my master_proc I'm getting...

Could not find stored procedure 'up_UpdateHFOTable'.

I guess I need a way to check if the up_UpdateHFOTable procedure exists, but I'm not sure how to neatly integrate a query for the sp into my code.

TIA

Me...

View 1 Replies View Related

User Already Exists ...

May 29, 2002

I gave a user access to a number of instances. I ended up restoring one of six instances he had access to. He no longer has access, as he did not exist for this instance during the backup. And I cannot provide him with access - I get a "User Already Exists" error. I've found some documentation on this, but I've had no luck.

Could someone tell me what to do?

Jeff

View 2 Replies View Related

How Can I Tell If A File Exists

Nov 17, 2005

I am going to use the BCP utility to export data from a table in a SQL Server 2000 database to .csv file, but I must determine if the file already exists. The BCP export code it going to be in a stored procedure and will be configured to run every two hours so I must have a way of checking if the file exists prior to executing the BCP instruction. If the file exists then no big deal just abort and two hours later it will try again, at some point the file will not exist and the export will export all the data since the last export - but normally this will happen every two hours; I just need to make sure that the "other peoples" utility has proccessed the file (and by definition deleted it).

It makes me no difference whether the best way to do this would be using a batch file that execute and returns true/false or a VBScript or if there really is a way to do it in TSQL. I just cannot seem to find any way.

View 1 Replies View Related

Insert If Not Exists

Mar 8, 2005

I have two tables that I have to compare:


Code:


Table:PR
WBS1 WBS2 WBS3
123-456 1000 01
123-456 1000 02
123-456 2000 02
567-890 2000 01
567-890 2000 02

Table:PR_Template
WBS2 WBS3
1000 00
1000 01
1000 02
2000 00
2000 01
2000 02

After Insert I should have:

wbs1 wbs2 wbs3
123-456 1000 00
123-456 1000 01
123-456 1000 02
123-456 2000 00
123-456 2000 01
123-456 2000 02
567-890 1000 00
567-890 1000 01
567-890 1000 02
567-890 2000 00
567-890 2000 01
567-890 2000 02





Basically, I need to insert the wbs2 and wbs3 where it does not exist in each wbs1.

What I have now will find the values that need to be inserted for a particular project but I don't know how to go through each project and perform the insert:

Select * from PR_template Where Not Exists
(Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')
Order by wbs2, wbs3 asc

Thank You

View 6 Replies View Related

Question About IF NOT EXISTS

Aug 3, 2004

Hello all,

I keep getting Invalid parameter binding(s). errors when using the following IF NOT EXISTS statement.

Any ideas?

IF NOT EXISTS
(select mobile_number
FROM tblCustomer
WHERE mobile_number = 'nParam'
AND op_id = 'opParam'
)
BEGIN
INSERT INTO tblCustomer (
mobile_number, op_id
) VALUES (
'nParam',
'opParam'
)
END


Many thanks

View 1 Replies View Related

If Exists Column ?

Nov 30, 2005

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank you

View 4 Replies View Related







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