|
Use triggers[^].
While updating data in table Table1 (I sugggest you to rename this table 'Table1' and give it any meaningful name) execute trigger which do insert in historical_table1.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks a lot Blue_Boy;
I must document on triggers because its the first time I heard trigger in database, thank you very much.
if you know a course for beginners on the triggers thank you pass me the link. 
|
|
|
|
|
No problem,I am glad to help you.
Check this link.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi All,
I would like to create a user when he logs in , i can hide all the tables that has been Created Before.All the database Table and Stored Procedures will be Hidden.
Can you please advice?
Many thanks
|
|
|
|
|
He cannot be logging directly into the database to do this, so you need to create a login page and use a known user name to see if he already exists. If not then you the need to create the login and user with the appropriate permissions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%'
('Smith (Hon. Member)John' will come from the user's input)
Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods).
I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces?
Hope I have explained this OK. Thanks in advance for any help.
|
|
|
|
|
Not ideal, but you can nest the replace statments for a quick fix:
replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','')
produces: SmithHonMemberJohn
otherwise you could always write a function to remove any non alphanumeric characters.
I don't know of any pattern matching within the replace statement though.
|
|
|
|
|
I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!
|
|
|
|
|
Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:
SELECT tblContacts.*
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Great
Thank you both for your help.
|
|
|
|
|
Hi,
This must be real simple, but I'm having a blackout.
I have two tables:
Names:
ID Name
1 John
2 Peter
3 Carl
4 Bernard
Nicknames:
ID NameID NickName
1 1 Bonny
2 1 Bobby
3 1 Jo
4 2 Pete
5 3 Bab
6 3 Foo
Now I have a search running, which looks for a certain string in both the nickname field as well as in the name field.
But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.
NameID NickNameID Name Nickname
4 null Bernard null
1 1 John Bonny
3 5 Bernard Bab
I fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution.
Does anybody have the correct solution to this issue?
|
|
|
|
|
You say that the select should return only one nickname. In your example, why did the select return Bonny for Bernard, not Bobby. Is there somekind of logic or is it simply random.
|
|
|
|
|
Random, I don't care which one he returns.
|
|
|
|
|
Okey, then you could use normal outer join between the tables and for example check that there's no greater nicknameid for the same name (using for example correlated not exists clause).
|
|
|
|
|
gnjunge wrote: I have a dirty subselect
I hope this T-SQL is not "dirty" but I couldn't make shorter,I hope too this T-SQL will help you.
select id,<br />
(select top 1 A.ID from nicknames as A where A.nameID = names.ID) as NicknameID,<br />
name,<br />
(select top 1 A.nickname from nicknames as A where A.nameID = names.ID) as Nickname<br />
from names<br />
where (names.name like 'b%')<br />
or names.id in (select C.nameid from nicknames as C where C.nickname like 'b%' ) </code><br />
<br />
Result is :<br />
<br />
<code>1 1 John Bonny<br />
3 5 Carl Bab<br />
4 NULL Bernard NULL
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This seems like it would be very slow, or isn't it? I guess in the end the names table would contain around 10.000 records,whereas the nicknames table would contain around 50.000
|
|
|
|
|
gnjunge wrote: This seems like it would be very slow, or isn't it?
Did you try to run in your real data and to see if is it slow?
For data which you post,query is not slow.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
In many to many relationships, I often need to add one record into a table and then insert multiple records into another table and finally connect them using a third table.
All these data are related to each other and either all or non shall be saved. To do this I have to use transactions and rollback upon failure.
To do transactions I can either do it inside my stored procedures(sql) or inside application(ado.net). If I do it in my application it will increase communication with database over network since for each record I call a sp remotely and transfer data.
If I use transaction inside a stored procedure(and I prefer this), I have to pass all data related to one insert to that proc which includes multiple records(one to many relationship).
The problem here is: What is the best way of passing multiple records to one stored procedure? using an nVarchar parameter and splitting data inside the proc is not my favorite way, if there is any other way around?
If there is a better way of doing the whole thing instead of passing one parameter or using ado.net transactions I'm eagerly waiting to hear.
Thanks a lot in advanced.![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
Personally, I prefer to do it from the application. In the Data Access Layer of the application, I'd have the control of the transaction along with the creation of the parent and child rows, then the rollback or commit of the transaction. You have to get the data of what you want to insert to some type of execution on the database server, one way or another. I think passing the data for the child rows in some kind of delimited format in an nVarchar(max) (or the like) would defeat one benefit of stored procedures: performance. It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation. I'd prefer the database to be 'dumb' and only do small, specific tasks like 'insert a new X', 'update Y', 'delete Z', etc; with the application on top of that putting those smaller building blocks together.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks for the help.
Ben Fair wrote: It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation.
Exactly. That's why I don't like that.
It seems there is not any third way. I don't like doing it in application but packing and unpacking is even worse, IMO. I was wishing there to be some other way that I don't know.
By the way, how do you implement it in app? I know it this way:
SqlConnection cnn = new SqlConnection("") ;
cnn.Open();
SqlTransaction trans = cnn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.Transaction = trans;
try
{
trans.Commit();
}
catch (SqlException)
{
trans.Rollback();
}
I don't like this because it mostly results in a big function dealing with multiple procedures and all their parameters. It's difficult to keep it clean or automate it's code generation, or maybe I'm missing something here.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
|
I agree with what Ben Fair said, especially about transaction boundaries. I never get used to beginning and ending a transaction inside a database since then the client looses control over the ACID logic.
However passing several record with a single call is also a performance issue so it's benefitial to use such logic. One way to do it is descibed in an article I wrote: How to pass multiple records to a stored procedure[^]
Hope it helps,
Mika
|
|
|
|
|
Great article Thanks for sharing. This definitely solves the issue I had.
What a good coincidence to asked the question and then exactly at the same day you posted the great article.
I like database related stuff(All CRUD operation) to be inside database. I use auto generated code in my application to access stored procedures. This both increase coding speed and also makes it cleaner and easier to maintain(It's easier to modify a stored procedure later instead of the application that needs recompilation.) I have this logic: A CustomerAdd stored procedure has to add a customer along with it's contact information(for instance) that might be more than one record. Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data? This is my opinion. Actually I mostly hear people advise what you also stated, but I'm still not motivated enough to do it the other way.
Thanks again.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
