|
Apurva Kaushal wrote: yep and there exactly I got confused and gave that solution.
Don't worry about it. It wasn't explained too well. You sometimes need to read something like that a couple of times to really understand what is wanted.
|
|
|
|
|
Colin Angus Mackay wrote: You sometimes need to read something like that a couple of times to really understand what is wanted.
Yep that is correct.. Thanks for the suggestion.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Now it looks like we were both a little bit correct and a little bit wrong.
C'est la vie!
|
|
|
|
|
You can put a select statement inside the IN expression
SELECT *
FROM Table1
WHERE SomeColumn IN (SELECT Something FROM Table2)
And no need for pesky dynamic SQL.
|
|
|
|
|
Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this
table1
col1
hyderabad
delhi
mumbai
table2
col1
hyderabad,chennai,bangalore
bangalore,kolkata,delhi
bangalore
mumbai,hyderabad
now if I give select * from table1 where col1 in (select col1 from table2)
i will not get the output
can u checkl it once
Regards,
Uma
|
|
|
|
|
You can create a table-valued function which uses a loop to split the csv list in table2 and insert them individually into a table variable.
<br />
CREATE FUNCTION [dbo].[function_string_to_table]<br />
(<br />
@string VARCHAR(MAX),<br />
@delimiter CHAR(1)<br />
)<br />
RETURNS @output TABLE(<br />
data VARCHAR(256)<br />
)<br />
BEGIN<br />
<br />
DECLARE @start INT, @end INT<br />
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)<br />
<br />
WHILE @start < LEN(@string) BEGIN<br />
IF @end = 0 <br />
SET @end = LEN(@string) + 1<br />
<br />
INSERT INTO @output (data) VALUES (SUBSTRING(@string, @start, @end - @start))<br />
SET @start = @end + 1<br />
SET @end = CHARINDEX(@delimiter, @string, @start)<br />
END<br />
<br />
RETURN<br />
<br />
END<br />
<br />
Then you can do this:
<br />
DECLARE @values VARCHAR(MAX)<br />
SELECT @values = col1 FROM table2 WHERE FOO = 'BAR'<br />
<br />
SELECT * FROM table1 <br />
WHERE col1 IN (<br />
SELECT data FROM dbo.function_string_to_table(@values, ',')<br />
)<br />
<br />
|
|
|
|
|
http://www.projectdmx.com/tsql/sqlarrays.aspx[^]
You should be able to use CharIndex as the article in the link above suggests as one solution.
select t1.*
from table1 t1
join table2 t2 on charindex(',' + t1.col1 + ',' , ',' + t2.col1 + ',') > 0
|
|
|
|
|
Hello,
I am developing a dictionary library and utility for a heavy-duty linguistic application. Because the wordlist is used very intensely, I am preloading it at startup. There are approx. 1 million records in the wordlist table, each containing a textual representation of a symbol, accompanied by a few numeric fields. There are other tables as well, but they are accessed less often and don't have to be preloaded.
What database management system would you recommend for my application?
I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds. I haven't tried the new SQL Server, but I suspect it'd be similar in efficiency to MSDE. Crafting a custom storage solution could be nice, but requires significantly more resources than a ready-made thing.
What do you think?
|
|
|
|
|
Bartosz Bien wrote: I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds.
What is more important to you: The speed of the pre-load operation, or the speed of retrieval?
To me a "pre-load" operation is something that happens in advance and is not time critical. If you need very fast search and retrieval then having a slightly slower preload operation where it properly indexes everything for you is much more advantageous.
You might also want to consider SQL Server 2005 Express Edition instead of MSDE.
|
|
|
|
|
hi,
Suppose, a dataset is filled with some data from the database, after that some changes where done to the database. But the value in the dataset will be the same as before.
The updated data has to be obtained form the database.
Is there any solution for problem?
sooraj
|
|
|
|
|
Actually the dataset is not connected to the database once you get the data. To update the dataset with the current data you need to get the data again from the database.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Google on cocurrency issue with dataset for this. There are plenty of workaround available in this workaround.
saud
|
|
|
|
|
Hi, I want to know if I made a transaction that include a SQL statement which delete some data from a table in SQL DB, and then i committed this transaction, can I return back that deleted data? or they are missed????
Thanks alot
Hamody
|
|
|
|
|
Once you delete the data and commit the data, the original data is gone. If this is not the behaviour that you want, you need to use a different method to "remove" the data. Normally this is done by setting a flag to mark the record as deleted somehow. This means that all the selects that you do for live data would also need to use this flag.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks a lot for your immidiate reply ....
May you explain me the flag you wrote? or what do u mean by a flag?
Thanks alot
Hamody
|
|
|
|
|
Mohammad Daba`an wrote: May you explain me the flag you wrote? or what do u mean by a flag?
The flag is some attribute in the table that defines whether or not this is a deleted record. For instance:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Rule]
GO
CREATE TABLE [dbo].[Rule] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Name] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Description] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[Solution] [int] NULL ,
[Deleted] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
The Deleted flag is N for live records, and Y for deleted records.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
well I'm not too sure if u can actually retrieve a deleted data after it's been commited, may be its possible throught some complex kind of operation in the database logs but its not in my knowledge
but I have an idea for this kind of situation... if you can maintain a log for urself by maintaing a table for loging the insert, del,update operations and setting up triggers on that table to insert a new record after every del, insert, update operation so late u can view every single operation a user performed... and do what ever u want with that too
hope it helps,
Rocky
|
|
|
|
|
thanks a lot dear for your good idea, but i don't know, i think that there s a solution for retrieving back any deleted data from any DB table, i think it is allowed for a specific period, i mean that may be you can return back ur deleted data after a specified time since ur delete.
do u know any way we can do this
Thanks alot
Hamody
|
|
|
|
|
You could always use a product like Log Explorer[^].
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
well no dear I dont know, if I knew I would have told u already.
|
|
|
|
|
How to deploy SQL Server 2005 - SQL Server Integration Services into SQL Server
|
|
|
|
|
hi,
just take a look at this article:
http://www.sqlis.com/31.aspx
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
I Have Created the Windows Form with SqlConnection objects,SqldataAdapters,Command objects ,DataSetObjects everything.I have also displayed the datas from the database into the dataGrid.But the problem is Iam not able to transfer the same datas in to the word document.Please find me the solution
With Regards
<b>sashi:</b>
Thankyou
|
|
|
|
|
hi sashi,
go thru these links:
1.http://www.codeproject.com/office/#Microsoft+Word
2.http://www.google.com/search?hl=en&q=Displaying+the+datas+from+the+database+to+the+word+Document+in+Visual+Studio.Net+2005
3 http://sylk.qarchive.org
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Hi,
I need to access SQL Server 2005 data from the browser by just supplying a URL. IS this possible? . I see that it was possible in SQL Server 2000 using the IIS Virtual Directory MAnagement. I have looked into SQL Server Web Services in SQL Server 2005 but, that does not solve our purpose. I'm looking for direct access from the browser using a URL? Please Help.
Thanks and Regards,
Sindhu
Sindhu
|
|
|
|