I have a table storing only 2 FKs, let's say PID, MID
Is there any way that I can check distinct data before row is added to this table?
For example, current data is
PID MID
------------
100 2001
100 2005
101 3002
102 1009
102 7523
102 2449
If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?
Thanks.
This is probably a silly question to most of you, but I'm in the processof splitting off years from a large DB to several smaller ones. Some ofthe existing smaller DBs already have most of the data for theirrespective years. But some of the same data is also on the source DB.If I simply do an insert keying on the year column, and a row beinginserted from the source DB already exists in the target DB, will aduplicate row be created?And if so, how can I avoid that?Thanks,John Steen*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedHow can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.
View 6 Replies View RelatedI have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.
The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.
Here is a sample of some data:
User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3
Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:
User1 Group1
User2 Group2
User1 Group1
I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.
Is there a simpler way to prevent duplicate entries in a table using sql?
Thanks a lot,
Chris
Hi,I need to enforce that a table does not have "duplicates" for aspecific status type in the table.If the column "STATUS" = 2, then there can not be more than one rowwith a specific "ID" column.I can not use a unique key constraint because duplicate values for thiscombo of columns is valid for the status = 1.Just when the status = 2, there can not be any other rows with the sameID and status = 2.Any ideas?-Paul
View 3 Replies View RelatedHow can I prevent duplicate records in the query
With
Property AS
(
SELECT
*
FROM dbo.MulkiyetBase
),
Document AS
(
SELECT
[code]....
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis.
INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD)
(SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B))
When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax.
Can anybody help me out?
Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
Cheers!
I would like to know what options I have with regards to trapping a duplicate record
before it tries to post to a SQL database. I have set the column to unique in SQL. But
when I try to use ASP and post a duplicate record I get a system error. I would like to
just create a referential error to notify the user that they cannot post a duplicate record
please try again. Any help would be greatly appreciated.
RT
Have a table that is used during a conversion. It is hit pretty heavily with inserts by mulitple session ids. For performance reasons I cannot add a unique constraint on the column that is getting duplicates (it is an encrypted cell in varbinary).
I do not want duplicates in this Encrypted Column. So before each insert the insert programs reads the table and verifies that the Encrypted Column value does not already exist. But unfortunately several duplicates are falling through the cracks. What are my options for not allowing duplicates?
I have a web app that calculates tax filing status and then stores data about the person.
Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()
And that is it.
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message
Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated.
The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key.
What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database.
Here is my code. pls modify it to achieve the said problem
if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
Hello,
is there a way to say to SQL Server to make the data not readable?
Regards
Markus
I have a table with 3 columns: ID, Status, DateTime.
I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted.
If the procedure was to be run a second time in hour window I do not want any Status to be inserted.
Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00.
Does anyone know if there is anyway I can gaurd against this?
Suppose I have a database storing some confidential information, such
as legal information, medical or financial records, etc., and a
Web site with a membership system so that only authorized users can
view the information.
I understand I can encrypt the information, and the user's passwords,
so that if the database is compromised it still shouldn't be possible
for an outsider to view the confidential information.
However, what about people who have legitimate access to the database,
such as the DBA, Web developer, etc., but who should not be able to
view the confidential information? For example, even though the
user's password was encrypted, what would stop the DBA from replacing
the user's password with his own (encrypted) password, then logging in
and viewing the user's info, then copying back the original encrypted
password? Or, adding a new user for himself with whatever
permissions he chooses?
1. How to estimate how many free RAM I need. If I plan to create five 2 Gb memory optimized tables...10 Gb RAM for data and how many additional memory?
2. How to prevent memory overflow if there will be too much data in those tables? I dont want that my server down entirely
hi..
How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator
If a user is a member of a role which would allow him to execute a proc which updates a table, and he is then granted db_denydatawriter , can he still update the table through the proc? SS2000 SP4.
Thanks,
michael
Since posting this I tested the scenario and YES! The user was still able to execute an update proc even though the user excuting the proc was granted db_denydatawriter.
Hi, I have a stored procedure that looks like this:...WHILE @@FETCH_STATUS = 0BEGINDECLARE @MyCount int ; EXEC spLoanQuestionnaireCriteria @AuditSelectedLoanID, @Criteria, @MyCount OUTPUTEND ...SELECT * FROM #Categories... Executing this stored procedure will return me 1 table for each time the EXEC statement is called that only has on column (MyCount)I really don't need this data to be returned, it is only used for some internal calculations in the stored procedureThe stored procedure should only return the results from SELECT * FROM #Categories in 1 table.Is there a Keyword I can use to exclude the EXEC results being returned to the dataset? Thanks in advance,Andre
View 2 Replies View RelatedIn my database some of the store procedures getting the data from xml nodes.so I need to implement the validation to xml data for prevent sql injection.
View 0 Replies View RelatedI'm trying to find a way to maintain the data that was UPDATEd in a table from being overwritten.
I used UPDATE in a table to include missing data. However, since I do not have access to the original table from where the data is derived, when I run my query, it knocks out the data in my table that was updated, because it is linked to the orginal table that has the blank fields. A few users do not have an employee email, and this has to be updated with a personal email account for now.
My update works fine, but is there a way I can use the statement in my query so that it updates each time it is run to maintain the user email address? This will solve the problem of not having to change the original table that I do not have access to.
if exists (select * test..dbo.Employee)
truncate TABLE
test.. dbo.Employee
insert into test.. dbo.Employee (EmployeeNumber
, EmplUsername
[Code] ....
I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.
View 5 Replies View RelatedI represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.
My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.
Our database structure is a trade secret and we cant reveal the structure to the client.
please answer this question by email to me at farhandotcom@gmail.com
Thanks & Regards
Farhan
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
I have 2 tables in my database.
one is Race table and 2nd one is Age Range.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
Hi everybody,
In our datbase we have a table with text data type.Help me if anybody knows how to insert text data into text data type of sql server.
i am able to modify and retrive but i am not able to insert text. please if u have idea, please give me reply asap.
Thanks,
Giri
I have some website work lined up and it involves some simple modifications to a MS SQL 2000 server. What I'll need to do is add some new data fields and insert some data.
I have some experience with databases - MS Access and MySQL, but I have never used or seen MS SQL 2000. My question is, is this a relatively simple thing to do for someone who hasn't used it before? I can do these things quite simply in Access or MySQL, so is MS SQL 2000 going to be any different?
Also, does anyone know of any free tutorials online that would help me out?
Thanks
Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
Hello guys! Is it possible to duplicate a primary key?
I would like my database to accept data with the same primary key.
Is it possible?
How do you declare ON DUPLICATE KEY UPDATE?
Please help me. Thanks in advance.
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record.
Thanks.