I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
View 6 RepliesI have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
View 1 Replies View RelatedI know there is a way to determine if a file exists using T-SQL, but I can't seem to find a way to determine if a directory exists. I need to be able to determine this so I can delete the directory if it already exists before I run other queries.
View 17 Replies View RelatedGreetings all.
How can I check for file exitence in a specified folder using TSQL?
I can think of a couple of ways to do this so any feedback would be appreciated.
Load the contents of a folder in to a temporay table and check the table for file name. I can do this with something like:
insert into myFiles
exec master..xp_cmdshell 'dir /B /OND filePath*.txt'
Or maybe use OLE Automation Sproc? But I am not sure which method I should use.
Please advise.
I can't seem to get this work. I'm using SQL2005
I want to check if a record exists before entering it. I just can't figure out how to check it before hand.
Thanks in advance. Protected Sub BTNCreateProdIDandName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNCreateProdIDandName.Click
' Define data objects
Dim conn As SqlConnection
Dim comm As SqlCommand
' Reads the connection string from Web.config
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)
' Check to see if the record exists
If
comm = New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @BuilderID) AND (OptionNO = @OptionNO)", conn)
Then
'if the record is exists display this message.
LBerror.Text = "This item already exists in your Option List."
Else
'If the record does not exist, add it. FYI - This part works fine by itself.
comm = New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@BuilderID, @OptionNO, @OptionName)", conn)
comm.Parameters.Add("@BuilderID", System.Data.SqlDbType.Int)
comm.Parameters("@BuilderID").Value = LBBuilderID.Text
comm.Parameters.Add("@OptionNO", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionNO").Value = DDLProdID.SelectedItem.Value
comm.Parameters.Add("@OptionName", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionName").Value = DDLProdname.SelectedItem.Value
LBerror.Text = DDLProdname.SelectedItem.Value & " was added to your Option List."
Try
'open connection
conn.Open()
'execute
comm.ExecuteNonQuery()
Catch
'Display error message
LBerror.Text = "There was an error adding this Option. Please try again."
Finally
'close connection
conn.Close()
End Try
End If
End Sub
I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.
I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.
I want to write a query that will check each row in the third table to see if
a) the employee exists in the Employees table
b) the company exists in the Companies table and
c) the employee is listed under the correct company
The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.
Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.
Anybody got any ideas?
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
Order by pg.grid_id
I am getting an error between the IF THEN Else statement. The copy works after the THEN! I need some help!
Ernie
I have two tables with script in a view. 1. newusers 2. blacklist I want to compare that "PresentEmail, RemoteComputer,Email" thats in newuser table is not in blacklists, and if this is true, then copy from newuser (the record not on blacklist) to blacklist using a where statement for second condition.
IF
(NOT EXISTS
(SELECT *
FROM newusers AS d
WHERE ISNULL(PresentEmail, 'NULL') IS BadEmail))
THEN
Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)
Select NewEmail, RemoteComputerName, PresentEmail
From newusers
Where Account_Active = 'Spammer'
Else
GO
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'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'.
Any ideas how to do this?
-D
I have a table called as Events and below are its columns
ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar
IsHoliday bit
This is a master table where the admin would enter the Events/Holidays for the entire year.
The data would be as below:
IDEventFromdateEventTodateEventDesc isHoliday
126-01-201526-01-2015RepublicdayYes
230-01-201531-01-2015TeamOutingNo
301-05-201501-05-2015Labour day Yes
Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"
How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015
Can we exclude a non-working day or a sunday.
Leavedetails table to insert leaves applied by employee is as follows
LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar
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.
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 have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.
To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.
Any thoughts would be great!
Thanks for your time:
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...)
BEGIN
Insert into dbo.Items2Fix ...
From inserted i
END
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
View 4 Replies View RelatedHere's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks.
Function DoesUserExist(ByVal userName As String) As Boolean
Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
Dim Cmd as New SQLCommand(queryString, sqlConnection)
With Cmd.Parameters
.Add(New SQLParameter("@username", username))
End With
sqlConnection.Open
Dim blHasRows As Boolean
Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.RecordsAffected > 0 then
blHasRows="True"
else
blHasRows="False"
End If
Return blHasRows
End Function
I am using a script component to retrieve some values from a server. This script component using Script task which uses VB.NET code. I have tested the VB.NET code and it returns a non null value for a string field 'sentence'. The 'sentence' feild is then passed on to an OLE DB Command transformation to be stored into the database along with a couple of other feilds.
The OLE DB Command transformation uses a stored procedure to store these feilds into the database. But when I check my table in the database, all other feilds are stored properly other than the 'sentence' feild which is null.
I am wondering what could be causingthis problem.
Thanks
I know how to select the most recent row from a database:
SELECT TOP (1) Location, Date FROM Images ORDER BY Date DESC
But how do I select the second to most recent? or the third most recent? or the 4th, ect, ect, ect.
There must be some method to it, anyone have any suggestions?
Hi,
Do you have any idea if there is / where can I find web based data entry into SQL 7.0 database. What I'm looking for is something like Oracle Forms but for SQL 7.0.
Thanks in advance,
Boaz
how to prevent the insertion of the same entry of an unique value into the database?
assume that I have a primary key username " abc" in the database
and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated..
how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
Any idea what this error is?
error 911: could not locate entry in sysdatabases for database 'msdb'. No entry found with that name
Thanks.
Sa
I am getting the exception - System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database. Does anyone has any idea, how to resolve this?
Thanks
I am very VERY new to using Visual Studios...
I have a SQL database with a table in it "table1" inside the table there is a field "ID"
I have made a new website project thing, and I have a textbox and a submit button
What I want to do is make a function that will check if what the user has typed into ID is valid, e.g. it exists in the database
Can anyone point me in the right direction about how I could do this?
Thanks very much
Which is the best way to check the size of a database from Transact SQL ? (Selecting from Sysfiles gives a totally different result compared to right-clicking a database and selecting Properties. It's the result from the latter than I want to obtain through Transact sQL.)
View 8 Replies View RelatedI have written some code that selects all the databases on our server then uses a while loop to execute some dynamic sql to query a table in each database. The problem is some of the databases dont have the correct permissions so the query breaks. Is it possible to check to see if the database has the correct permissions before I execute the dynamic SQL and if it doesn't move onto the next one in the list?
View 1 Replies View RelatedHi all,
We have been checking the database by dbcc checkdb,through a automated task
on our production server (nightly task run everday)d we always getting the message given below.
Msg 625, Level 20, state 1
Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0.
If anyone knows about this error message, can you kindly post the message.
Thanks a lot.
Jay.
hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
I am fairly new to SQl and PHP and have a question. I have a database that we enter in when a person buys a season ticket for our baseball team. I want to check and see who bought in 2013 but has not yet bought in 2014. How do I do that?
THID is the ticket holder ID and there is a year. I can do the select * from Passes where Year = '2014' and get everyone who has bought in 2014. How do I compare that with the 2013 and show people who have not bought yet in 2014?
Example entry:
THID PassType Year
15 2 2013
21 1 2013
10 1 2013
21 1 2014
With this - Ticket holder 21 has bought in both years so I wouldn't want his name to come up. I would want to see that ticket holders 15 and 10 haven't yet bought in 2014.
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
View 3 Replies View RelatedGreetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
Hi all,
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
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