Hi,
I have 2 tables in an SQLServer db.
I want to compare table A with table B and add any records that EXIST
in table B but dont exist in table A, to table A.
Can anyone help me with the SQL?
TIA
Hitcher
Hello,
it's me again :)
I've got a - what I think - simple question.
There is table A with Col1,Col2,Col3 and Table B with Col1,Col2,Col3
I want all rows from B in A. If a row already exist in A, then update all columns, else just insert the row.
Can someone please help me with a small syntax.
Thank you!
Hi,
I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).
I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.
Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I’m not sure how should I go about that problem. This is my stored procedure:
DECLARE @CatNo INT, @CatName varchar(10)
SET @CatNo = 2
SET @CatName = 'bracket'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END
SET @CatNo = 3
SET @CatName = 'cable'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END
(... Goes for another 37 categories)
Thank you for help.
Kooba
I am trying to determine the next registered session of a student so Ican calculate the number of skipped sessions.Scenario: I have a student registration summary table. One row foreach student and the student's registered session. I want to update agiven row with the next higher registered session (into a field callednext_registered_session_skey if the row exists). I can then use thediff of the skeys to determine how many sessions the student skippedfor each registration period.Example: Student X registers each fall for one session for 4 years.The file might look like:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100null123456789 200301 104null123456789 200401 108null123456789 200501 112nullI need to update the NEXT_REGISTRED_SESSION_SKEY so I end up with:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100104123456789 200301 104108123456789 200401 108112123456789 200501 112nullI can then say SESSIONS_SKIPPED = NEXT_REGISTRED_SESSION_SKEY –SESSION_KEY (logically speaking, not syntactically)This is what I have so far as example:UPDATE F_REGISTRATIONSET NEXT_REGISTERED_SESSION_SKEY = (select top 1 nextr.session_skeyfrom f_registration rinner joinf_registration nextron r.student_skey = nextr.student_skey and nextr.session_skey[color=blue]> r.session_skey[/color]order by r.session_skey desc)WHERE STUDENT_ID = '577665705';SELECT student_skey, student_id, session_id, session_skey,next_registered_session_skey, * FROM F_REGISTRATION WHERE STUDENT_ID= '577665705' order by session_skey descRESULTS:STUDENT_SKEY STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTERED_SESSION_SKEY125137 577665705 200404 309 311125137 577665705 200403 308 311125137 577665705 200402 307 311125137 577665705 199804 285 311125137 577665705 199803 284 311125137 577665705 199802 283 311125137 577665705 199704 281 311TIARob(I restricted with the where = ‘577665705' so I did not have to waitto update all the rows)
View 6 Replies View RelatedIn MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
View 6 Replies View Related
I had implemented as in the link to insert or update
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
What i want to know is... how can i assume there are no duplicate records.
I used Distinct keyword and queried it showed me all are distint but some where i find some duplicates just don't know why i am having when i look at the data both are exactly same...
Please let me know how can i fix it.
Urgent..
Thanks
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?
View 2 Replies View RelatedI've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
Phil
I'm trying to write a script that would only update a column if it exists.
This is what I tried first:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
UPDATE dbo.Enrollment SET nosuchfield='666'
END
And got the following error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nosuchfield'.
I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.
The only way I can think of to get around this is with:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
declare @sql nvarchar(100)
SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666'''
execute sp_executesql @sql
END
which looks a bit awkward. Is there a better way to accomplish this?
I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;
[code]....
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
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.
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
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.
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 RelatedHi,
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
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.
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
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
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
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!!
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,
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
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.
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.
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
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 RelatedI need to insert records into the table parSalesDetailModifier fromOLDparSalesDetailModifier where (1) those records DO NOT exit inparSalesDetailModifier and (2) those records have a parent record inparSalesDetail.When I run the below query I get the error message that I am violatingthe 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'dreally appreciate it.Thanks,Jennifer-------------------------------- STORED PROCEDURE-------------------------------CREATE Proc LoadModifier2@S datetime,@E datetimeASINSERT INTO ParSalesDetailModifier(parSalesDetailModifierID,parSalesHdrID,parSalesDetailID,ModifierType,POSModifier,Condiment,CondimentPrice,UnitNumber,BusinessDay)SELECTOLD.parSalesDetailModifierID,OLD.parSalesHdrID,OLD.parSalesDetailID,OLD.ModifierType,OLD.POSModifier,OLD.Condiment,OLD.CondimentPrice,OLD.UnitNumber,OLD.BusinessDayFROM OldParSalesDetailModifier OLDWHEREEXISTS( SELECT DET.parSalesHdrID,DET.parSalesDetailID,DET.UnitNumber,DET.BusinessDayFROM ParSalesDetail DETWHERE OLD.parSalesHdrID = DET.parSalesHdrIDANDOLD.parSalesDetailID = DET.parSalesDetailIDANDOLD.UnitNumber = DET.UnitNumberANDOLD.BusinessDay = DET.BusinessDay)ANDNOT EXISTS( SELECT NEW.parSalesHdrID,NEW.parSalesDetailID,NEW.parSalesDetailModifierID,NEW.UnitNumber,NEW.BusinessDayFROM ParSalesDetailModifier NEWWHERE OLD.parSalesHdrID = NEW.parSalesHdrIDANDOLD.parSalesDetailID = NEW.parSalesDetailIDANDOLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDANDOLD.UnitNumber = NEW.UnitNumberANDOLD.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]GOCREATE 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]GOCREATE 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,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUBMAYO','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,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NOONIN','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,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into parSalesDetailvalues(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-0110:00:44.000','2003-12-01')-------------------------------- END INSERT INTO TABLES-------------------------------
View 7 Replies View RelatedHi,
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'.
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)
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,
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
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