|
Do a select to check if the duplicate criteria is there and then insert if nothing matches. You could do this with an IF NOT EXISTS query. Alternatively, create a unique index on these fields and rely on the database catching it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Are u checking for duplicate values in a Database or a table? If it is a table the you use the following script.
SELECT
count(Column(s))
FROM
table_Name
ORDER BY Column(s)
GROUP BY column(s)
HAVING Count(Column(s)) > 1
Hope this helps.
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data.
UPDATE dbo.EmpWorkHours
SET Emp_Timeout = getdate()
where Emp_Timeout in (SELECT TOP 1 Emp_timeout
FROM dbo.EmpWorkHours
WHERE FK_Emp_Login_ID = 'MDoe74'
and Emp_Timeout IS NULL
ORDER BY RecordNum DESC
)
The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist.
Any assistance will be great appreciated.
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
hi
Check whether the field Emp_Timeout is null or ' '(Space) in the table dbo.EmpWorkHours
Regards
Joe
-- modified at 1:50 Friday 12th October, 2007
|
|
|
|
|
Thanks for your response. I figured it out, using the code below.
UPDATE dbo.EmpWorkHours
SET Emp_Timeout = getdate()
where RecordNum = (SELECT TOP 1 RecordNum
FROM dbo.EmpWorkHours
WHERE FK_Emp_Login_ID = 'MDoe74'
and Emp_Timeout IS NULL
ORDER BY RecordNum DESC
)
What was wrong with it you may ask? Well, I was selecting the Emp_timeout field which was null. I was then telling to update a record where getdate() equals the value in the Emp_Timeout field. That would never happen because at the time of the query Emp_timeout field is NULL thus it will never equal getdate().
Hope this helps.
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Hi,
I m facing problem when I m trying to bulk insert record from back end and front end. it was working before but accidently my server has crashed and i have install every thing but now I m not able to Bulk insert through my store procedure.
Error Message :
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
please help why this error comes? what is the reason, is something i didnot install yet or something else?
Thanks in advance
I am software programming engineering student
and i want to develop in this field so Please help me whenever I want ur support.
Thanks & Regards
Mohammad Faiz Siddiqui
|
|
|
|
|
Of course it's urgent, whose question isn't ?
Have you considered checking your stored proc to see if the advice the error gives you is correct ? Did you consider posting the proc here so people have something on which to base their attempts to help you ?
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
This usually means you have a "begin tran" and you either don't have a commit or rollback or they are not getting hit when you execute the stored procedure. Leaving an open transaction on the database will cause major problems since the open transaction will keep others from being able to access the table(s) that were in the transaction.
Ben
|
|
|
|
|
Have u tried this:
Begin
StoredProc()
IF
@@Error = 0
Commit trans
Else
Rollback Trans
End
Hope this helps.
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Hello friends,
I want to connect to the remote database through asp pages. My db is made in sql server 2005. What could be the database connection string?
|
|
|
|
|
|
Hi,
I'm a little confused with my table relationships. I have a Customer table and Title table. Title is something like Mr., or Miss., etc.
I have a foreign key reference in the Customer table to the Title. How would I read this relation.
...for each customer there is only 1 title, and for each title there is only one customer, or
...for each customer there is only 1 title, and for each title there is one or many customers??
This is a 1:1 relation, or 1:M?
Please help.
Regards
|
|
|
|
|
This is how you read:
For each customer you can only have one title, and for each title you can have one or more customers.
|
|
|
|
|
As John says, a foreign key provides a one-to-many relationship. A one-to-one relationship doesn't make much sense as you may as well combine the tables into one. I'm not sure you can even create such a relationship because you'd have to simultaneously insert into both tables in order not to break the constraint. Anyone?
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: A one-to-one relationship doesn't make much sense as you may as well combine the tables into one. I'm not sure you can even create such a relationship because you'd have to simultaneously insert into both tables in order not to break the constraint. Anyone?
1-to-1 relationships can make sense in some circumstances when dealing with optimisation of very large rows, especially if some of the columns are only very rarely neaded. It is also useful when dealing with joining separately maintained systems that need to keep in sync (e.g. if you buy in a third party system and want to extend it without touching its database tables)
Typically it would actually be a zero-to-one because you insert one row then they other.
Parent
------
ID PK
Col1
Col2
Col3
Child
-----
ID PK FK
Col4
Col5
Col6
The Child row is optional, but there can be only one as its primary key is also the foreign key (which forces the oneness of the relationship)
|
|
|
|
|
This is a 1:M relationship. The reason being that you can have many Mr. or Mrs. Customers but a customer can only be ONE either Mr. or Mrs.
Anyone???
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Skanless wrote: Anyone???
Do you mean other than the two people who answered the question before you?
|
|
|
|
|
I want to compare 2 tables of different sql databases. How can i acomplish this task?
I request u to guide me for the same.
Gurudatta B. Shelke
|
|
|
|
|
if the databases are on the same server, you can just prepend the database name to the table in your query. eg. database1.dbo.Table1, database2.dbo.Table2.
To see what's different you probably want a full outer join.
Regards,
Rob Philpott.
|
|
|
|
|
Am not aware of any database/table comparison tool that is bundled together with the commercial/non-commercial SQL Server but some third party tools exist. You can Google for 'ApexSQL Diff', 'SQL Delta', or 'SQL Compare' among many others. They are commercial but you first get a chance to evaluate.
|
|
|
|
|
|
Hi,
I am confused as when to use varchar and nvarchar. Can there be a performance issue when maybe choosing nvarchar above varchar and vice versa? Please provide examples when you will use both??
Thanks
|
|
|
|
|
Put simply nvarchar is unicode - 2 bytes/character, varchar is ASCII - 1 byte/character.
so nvarchar takes twice the space of varchar and consequentally won't be as quick. If you need multilanguage support go for unicode as this can hold just about all character sets on the planet. If its a plain english app and always will be stick with varchar.
Regards,
Rob Philpott.
|
|
|
|
|
You never know when Mr Gütenheim needs to register on your site, or when you add the new product "übermaster 10000".
English only is not always so cut and dried. Use unicode unless theres a real performance or storage reason not to.
|
|
|
|
|
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
I am fighting against the Universe...
Reference-Rick Cook
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
