I am using the following code to insert records into a destination table
that has a three column primary key i.e. (PupilID, TermID &
SubjectGroup). The source table records all the pupils in a school with
(amongst other things) a column (about 50) for each subject the pupil
might potentially sit. In these columns are recorded the study group
that they belong to for those subjects. The destination table holds a
record per pupil per subject per term, against which the teacher will
ultimately record the pupils performance.
The code as shown runs perfectly until the operator tries to insert a
selection of records that include some that already exist. What I would
like it to do is, record those, which do not exist and discard the
remainder. However, whenever a single duplicate occurs SQL rejects the
whole batch. I know that my solution will probably involve using the
‘NOT EXISTS’ expression, but try as I might I cannot get it to work. To
further complicate things, the code is being run from within VBA using
the RunSQL command.
The variables ‘strFieldName’, ‘strGroup’ & ‘strTerm are declared at the
start of the procedure and originate from options selected on an Access
form.
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
I am new to msSQL and ASP, I need some help writing an SQL statement that will first check to see if a combination or record exists, if none found thant it will add it. I am working a section of a site that adds favorites to the database. Each user can have more that one favorite hence it has to check for that unique combination of the fields, UserID and FavID
My Code: Dim objConnection, objRecordset, strSQL Dim strFavID, strUserID strFavID = request.Form("favid") strUserID = request.Form("userid")
Set objConnection = Server.CreateObject("ADODB.Connection") Set objRecordset = Server.CreateObject("ADODB.Recordset") objConnection.Open Application("ConnectionString")
strSQL = "INSERT INTO FAV (UserID,FavID) (SELECT DISTINCT " & strUserID & " AS UserID " & _ strFavID & " AS FavID" & " FROM FAV)" & _ " WHERE " & strUserID & " & " & strFavID & " NOT IN (SELECT UserID, FavID FROM FAV)" response.Write(strSQL) 'response.End()
If strFavID <> "" Then On Error Resume Next objConnection.Execute(strSQL)
objConnection.Close Set objConnection = Nothing End If
This code is giving me a syntax error, how do I write the correct statement. I am using MS Access 2k
/*if key values exist don't insert new record*/ SELECT
/*if exists don't insert*/ CASE WHEN ISNULL(gradeId, -1) = -1 THEN INSERT INTO tblScores (gtStudentId, assignmentId, score) VALUES (@nStudent, @nAssignment, 0) END
FROM tblScores WHERE gtStudentId = @nStudent AND assignmentId = @nAssignment
tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.
I'm getting syntax errors when I click check syntax (near keywords insert from and end).
one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?
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 am trying to populate a table with 2 FKs as its PK (SiteID and ProductDescID). First 1) I add in all the products whose Manfacturer and Category are supposed to appear on the site and then 2) I add in all the extra products that are needed regardless of their manufacturer or category. The problem I am having is if the product has already been added to the ProductCatTable due to its Manufacturer or Cateogry but is also in the SatForceProduct table. The can’t insert duplicate PK error is thrown. I don’t know how to do this IF NOT EXISTS statement (or what ever else may be needed) so that I can check whether a line from the Forced table needs to be added. I am not passing in any parameters and I am expecting more than 1 line to be inserted in each of the statements. Please help -- 1) Populate INSERT INTO dbo.ProductCatTable (SiteID, ProductDescID) SELECT dbo.SatSite.SiteID, dbo.ProductDesc.ProductDescID FROM dbo.SatManu INNER JOIN dbo.ProductDesc ON dbo.SatManu.Manu = dbo.ProductDesc.Manu INNER JOIN dbo.SatCats ON dbo.ProductDesc.Cat = dbo.SatCats.Cat INNER JOIN dbo.SatSite ON dbo.SatManu.SatID = dbo.SatSite.SiteID AND dbo.SatCats.SatID = dbo.SatSite.SiteID 2) Add Force Ins IF NOT EXISTS(SELECT SiteID, ProductDescID FROM ProductCatTable WHERE ????????) BEGIN INSERT INTO dbo. ProductCatTable (SiteID, ProductDescID) SELECT SiteID, ProductDescID FROM dbo.SatForceProduct END Thanks in advance J
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)
I'm using MSSQL and PHP. I've got the following sql statement:
$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')
The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused: mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not. I tried:
In 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
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...
I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record. I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.
I'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.
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/
Here is the steps I should take... 1- Check for the log table and find run status ( there is a date field which tells the day run) 2- Lets say last day was 2008-05-15, So I have to check A1.DDDDMMYYY file exists in the folder for each day like A1.20080516,A1.20080517 and A1.20060518 ( until today) 3- if A1.20080516 text file exist then I have to move it to the table and same thing for other dates like if A1.20080517 exists I have to load it to table and so on
it looks like for each loop, first I have to get the last date and then I have to check the file exists for each date and if the date file exists then I have to load it into table...
Please tell me How can I do it. it looks complex looping...
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 ;
Louis writes "Is it possibble to write a SQL statement that returns "True" or "False" if a stored procedure exists. Basically write a SQL statement that checks whether or not the Stored Procedure exist in the database???
Hi,In my stored procedure I would like to do something like :IF EXISTS(SELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateId)BEGINSELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateIdSET @RetVal = -1ENDIs there a nicer way to do this, so that we don't have to do therequest twice ?Regards
Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:
IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ... from inserted i WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/ if NOT EXISTS (SELECT object_id('procedureName','p')) CREATE PROCEDURE [dbo].[procedureName] @location char(2) AS ...Procedure...
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p')) SELECT 'FALSE' ELSE SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'PROCEDURE'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@location'.
Hey,I'm writing some automated unit tests for a database app. The appcreates a database via script. I'm developing the system in atest-driven manner and need a way to determine if a parameter existswithin a stored procedure using SQL. Any ideas?Thanks,WillJoin Bytes!
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code: SqlCommand command = new SqlCommand(); SqlConnection conn = database.TypeLibraryDatabaseConnection; command.Connection = conn; // create the command to create the stored procedure command.CommandText = database.GetProcedure(node.Name); // create the stored proc in the database try { command.ExecuteNonQuery(); } catch { } command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
I Have a nifty little stored procedure that takes data from an ASP form post and inserts the data into two tables. However what I really need this to do before the insert is check whether there is already a record that matches some of the criteria, and if so returns an error "This Username already exists" and only if there isn't a record that matches the criteria is the record inserted.
my current stored procedure looks like this -
Code Snippet@siteid int,@companyname nvarchar(50),@address nvarchar(500),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(225),@url nvarchar(225),@companytype nvarchar(50),@billingcontact nvarchar(50),@name nvarchar(50),@AccountType nvarchar(50),@PASSWORD nvarchar(50),@AccountLive nvarchar(50),@EmployeeLevel nvarchar(50)ASDeclare @NewID INTINSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)SELECT @NewID = SCOPE_IDENTITY()INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone) The values that i need to check against are -
@siteid @email
in the table dbo.JBEmployee against columns JBESiteID & JBEUsername
What i would really like to do if a record exists is return the user to an ASP page, which contains all of the variables previously enterred -,
I need to find out if a Transaction ID exists in Table A that does not exist in Table B. Â If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. Â This would work easily if there were only one row. Â What is the best way to handle it if there are a bunch of rows? Â Should there be some type of looping?
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!
I am getting info from one table, CalibrationReview, that is not inanother table, tblEquipments. SELECT EquipmentNumber, Model, SerialNumber, Make, CalLabName, CalDateFROM CalibrationReviewWHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber) Now, I need to take these rows and INSERT them into tblEquipments,but with some conditions. tblEquipments has some contraints, so, the following needs to be done: Using dbo.CalibrationReview.EquipmentNumber, get CalibrationMaster.TestTechnology where dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID Then take CalibrationMaster.TestTechnology and read tblTestTechnology.testTechnology and get tblTestTechnology.id So, tblEquipments.testTechnology = tblTestTechnology.id OR 1 if not foundWHERE dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID and CalibrationMaster.TestTechnology = tblTestTechnology.testTechnology And similar for CalibrationReview.CalLabName. tblEquipments.calLab = tblLabs.ID where tblLabs.LabName = CalibrationReview.CalLabName I hope this is clear as I can write this in code behind, but it'smuch better using sql simply because it's faster and only needs tobe run once. Inheriting databases and combining all of them to develop a large.Net management system is fun, huh? Thanks for any input, Zath
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
