Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View 10 RepliesHello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View 4 Replies View RelatedMy problem is that the 'INSERT INTO' query that sends the records to thetable is dynamically compiled in VBA and and the target table has a twocolumn primary key. I have made a number of attempts at getting 'WHERENOT EXISTS' to cure the problem but so far without success and previouspostings have resulted in advice to create an 'ignore duplicates' index.This solved the problem in asmuch as it allowed the SQL to insert the records that did not alreadyexist but resulted in the message appearing every time the user ran thethe query. Whilst this is not a major problem it is vaguely irritatingand I would like to find a way to stop it happening. I suspect that thesolution may involve using the @@ERROR command but I am not sure of thesyntax.RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:
1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
View 18 Replies View RelatedHi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
truncate table [stg].[Nationality_1_Leaf]
insert into [stg].[Nationality_1_Leaf](importType, ImportStatus_id, BatchTag, Name)
values(1, 0, 'some batch' , 'American' )
select * From [stg].[Nationality_1_Leaf]
exec stg.udp_Nationality_1_Leaf 'VERSION_1', 1, 'some batch'
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
Hi All,
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar]
(2)-ReceivedTime [datetime]
(3)-Status [char].
Now here how to remove duplicate entry of same mobile number to MobileNumber field?
Regards
Shaji
How can i keep only one record from duplicate entry.
Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14
Here i want to keep only the top record and want to delete other 2 rows.
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Thanks,
I'm trying to modify a specific row in a table.
$paper_id = 2A
$query = "UPDATE book SET paper_id='$paper_id', title='$title'";
I'm getting this error <<query failed: Duplicate entry '2A' for key 'PRIMARY'>>
Structure:
Column: paper_id
Type: Char
Length: 20
Deafult: None
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1
I 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 RelatedHello All,
I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.
I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.
This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.
Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
Hi all,
SQL server allows to create as many as foreign key constraints on a same table for a same column.
Will this affect the design or performance in anyway ?
Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?
======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.
ALTER TABLE Books
ADD
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)
======================
Thanks is advance,
DBAnalyst
Hi
i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column
select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc
but i am not able to get the fks created more than once on same column refering to same pk
Thanks in Advance
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies View RelatedTask:
To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.
What's the quickest/cleaniest way to do this ?
thanks,
Clayton
While i execute the below query ,Entry is put in status_report table.But the same if i put in scheduling (Sql Agent) no entry is made in the table and the message success is shown in "Agent Job Activity".
Purpose of the sp is to transfer data from One Server to Another Server.
But if see in the Destination Tables No entry is made.
CREATE procedure RBI_Control_sp
as
begin
set nocount on
begin try
BEGIN TRANSACTION
insert into fin_wh..Status_report
(
[object_name],
row,
st_date
)
select
'Data Tranfer',
'Inprogress--',
getdate()
--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp
--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp
Update fin_wh..Status_report
set row= 'Success',
[error_message] = 'No Error',
end_date= getdate()
where row= 'Inprogress--'
COMMIT TRANSACTION
end try
begin catch
ROLLBACK TRANSACTION
Update fin_wh..Status_report
set row= 'Failure',
end_date= getdate(),
[error_message]= cast(error_number() as varchar) + '***' + error_message()
where row= 'Inprogress--'
end catch
set nocount off
end
Txs in advance
Not sure if this is the right place to post this but hopefully someone can help me. I would like to determine what the last automatically incremented ID is in a table I need to return it as a variable to a VB.NET program but I'm not sure what the SELECT statement would look like for this. Any help would be greatly appreciated.
View 4 Replies View Relatedhi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that,
I am using sql server 2000. thanks
Ali
is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?
View 1 Replies View RelatedEmpid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies View RelatedI need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies View Related Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8