|
Hi,
I am trying to write a stored procedure that takes a comma separated letter. I have a split function that returns the splitted letters. I have select some values from the tables in the database where the title starts with each splitted letter. I thought I should use cursor that contains each letter and in the while loop i put my select statement with the conditions.
Is this the correct way. Or are there any ideas for this?
thanks,
Regards,
shakthi
|
|
|
|
|
SELECT *
FROM Books B
WHERE SUBSTRING(B.Title, 1, 1) IN (
SELECT Letter FROM dbo.SplitFunction(@commaList)
)
IMHO, cursors are evil. They are sometimes necessary, like when you have to call a procedure for each row in a query or some other operation which can't be expressed in a subquery or join. But overall you should unless this is your situation you should look for an alternative. And this situation is required less now with the advent of the CROSS APPLY clause. SQL is best when it is batch processing. Anytime you are doing something in a loop you are asking for performance problems somewhere.
|
|
|
|
|
Very nice solution Mark.
I have one similar question: Do you have an idea how to handle such scenarios in which the comma separated list contains words of different lengths instead of letters.
e.g. Taking this book's problem as an example: Lets say we want something like:
Select * from Books where Books.Title.StartsWith( <br />
Select StartingString from dbo.SplitFunction( 'Microsoft Press', 'Aspress', 'Oreally', 'Sams' )
|
|
|
|
|
|
Thanks for the reply Mark, Let me go through your article and write you back.
|
|
|
|
|
Hi Mark,
It seems you didnt get my point completely. Your article converts a comma separated list into a table but my problem was how to match starting string from that table. Let us look at my original question again:
Select * from Books where Books.Title.StartsWith(
Select StartingString from dbo.SplitFunction( 'Microsoft Press,Aspress,Oreally,Sams' )
Note that I am not using an IN clause. I want some StartsWith function that T-sql doesnt have.
Also, note that the comma separated list does not contain equal-length strings so we cant do something like
WHERE SUBSTRING(Books.Title, 1, N) in (select .. from SplitFunction)
since N is not constant here.
Hope you get my point.
Thanks,
Mehroz
|
|
|
|
|
Hi All,
Could you please let me know how to set up a CSV file transfet from an FTP site to a Table in a SQL Database table ?
Do i have to use Ftp Bulk Insert Task? How do set up?
Please advice.
Thank you
|
|
|
|
|
The short answer to your question is, yes you will need the Ftp task. Use Ftp as your source and then you will need to choose the correct destination task (for example Sql Server destination). Most likely you will probably need to do some sort of data conversion as well. There are transformation tasks for this as well.
If you need anything more specific than that you will need to ask a question specific to a problem to are experiencing and describe why it isn't working.
I also suggest you get a book, or use Google to look for tutorials and HowTo topics on using SSIS. A forum is not the place for you to have someone do your work for you.
|
|
|
|
|
Thank you very much for your response.Its much appreciated.To be very specific i have used the Ftp Task to download the file from the FTP server to a local Pc where the Sql Database runs.The next step is to Upload the file on to the Sql table i have used Bulkinsert Task.I have manhed to upload a flat file (*.txt) file.However when i Use a Csv File excatly in the same format to the flat file It doesnot copy the data on to my table.
I hope its clear now.
Thank you
|
|
|
|
|
Are you getting any error messages? If so what do they say? Simply saying it doesn't work isn't enough information to go on.
|
|
|
|
|
Thank you Once again.On the Bulk insert progress it says : Bulk insert Task is Initiated ,and Bulk insert task is completed.However the actual data is not copied on to the table.Hope this will help.
Thank you.
|
|
|
|
|
View is a virtual table the database does not store view data ........
I got this line from a site.
I just want to know that does the database not store view data
but i created a view on a table and i opened that view and also it's showing data in the view.....
Plz explain
|
|
|
|
|
Hi,
Please know that a view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
Once you have defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
For more details please visit the following link and go through the article.
http://www.sql-server-performance.com/articles/dev/views_in_sql_server_p1.aspx[^]
Hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
I have to add a not null column in a table.
I am using
IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE column_name = 'xyz'
AND table_name = 'abc')
BEGIN
ALTER TABLE abc WITH NOCHECK ADD xyz MONEY NOT NULL
END
It gives me error
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'xyz' cannot be added to non-empty table 'abc' because it does not satisfy these conditions.
can anybody have any idea, what's wrong with my SP?
|
|
|
|
|
Nothing is wrong.
salon wrote: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'xyz' cannot be added to non-empty table 'abc' because it does not satisfy these conditions.
It says it all. You cannot add NOT NULL columns to an existing table unless you specify a default as, obviously, the new column will contain NULL.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you want to add a NOT NULL column you will need to create a new table, for example if your table's name is "abc" then create a table with the new schema (i.e. the new column defined as NOT NULL) and name that table "abc_new". Then copy the data from "abc" to "abc_new". You will need to either provide a default value thru a constraint or your copy proceedure. After you have completed the copy, drop "abc" and then rename "abc_new" to "abc" with the system stored procedure 'sp_rename'.
|
|
|
|
|
hi
i have created adialog based application.but i just found out recently that it is hard to link a database to adialog("such as displaying results in an edit box ")
Is it really that difficult??
and can some one tell me how to do it???
please i really need it ("its for my project and i will reach my time limit soon  ")
please >>>any help will be appreciated.
thank u in advance
Lahom
|
|
|
|
|
where add related tables automatically on diagram sql server 2005?
|
|
|
|
|
I guess you have to start from here [^]
I Love SQL
|
|
|
|
|
Hai,
Y'day i attend a written test for job.i got this question.
what is the ending statement for sql query(choose three)
1)alter,2)rollback 3)create 4)insert 5)delete.
|
|
|
|
|
I don't understand the question. What is meant by "ending statement"?
|
|
|
|
|
Don't take the job - the question does not make sense as stated.
However
Alter and Create modify the the database the others modify the the data is the closest you will get.
|
|
|
|
|
Hi Guys,
Havent been working much on triggers and well have to get one to work now, so need some help to get the holy grail.
I have a table called username and when ever a record have been changed I need to update the last update time in column lastvisittime. The problem is that with this trigger below it updates every record in the table and not just the single one that is being updated.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username] <br />
FOR UPDATE<br />
AS<br />
UPDATE username set lastvisittime = dbo.TINT(getdate())
Generally I need to know how do I find out what row was currently supposed to be updated? Isteadof updating the whole table as it does now.
Have been trying good old uncle google but didnt find any clue to it, so hope the experts here can help 
N.b. dbo.TINT(getdate()) is just a function that changes the datetime to an int value.
Cheers,
Ronni
modified on Saturday, April 5, 2008 6:57 AM
|
|
|
|
|
Okay found out myself.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username]
FOR UPDATE
AS
DECLARE @ID int
Select @ID = id from inserted
UPDATE username set lastvisittime = dbo.TINT(getdate()) where id = @ID
|
|
|
|
|
I design Data base in access 2007 and move it to Sqlexpress 2005 Database.
I can see the tables and all in sqlserver management studio.
But when I connect to the sqlexpress from Vs 2005 I do not see any stuff from access.
I am not sure what is going on here.
Both sqlexpress and Vs are on the same machine.
Cheers.
Tapas Shome
System Software Engineer
Keen Computer Solutions
1408 Erin Street
Winnipeg, Manitoba
Canada R3E 2S8
http://www.keencomputer.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
