Database

Re: SQL Stored Procedure INSERT with random records from a 2nd table

6-Jul-09 6:11

SELECT TOP 5 (*) from tbl_organisations order by newid()

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.

munklefish7-Jul-09 0:04

munklefish

7-Jul-09 0:04

Blue_boy,

Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.

i neeed help

mahmoudinirat5-Jul-09 23:32

mahmoudinirat

5-Jul-09 23:32

i have two tables the first has foreign to another like this
table1:
table1Id table1Name table2Id
1 mahm
2 kha 1

table2:
table2Id table2Name
1 cc
2 dd

i need the following result
table1Id table1Name table2Name
1 mahm
2 kha cc

Re: i neeed help

DoctorMick6-Jul-09 0:00

DoctorMick

6-Jul-09 0:00

select table1Id, table1Name, table2Name
from table1
left join table2 on table2.table2Id = table1.table2Id

Want to understand how to fine tune stored procedure in SQL Server 2005?

Sasmi_Office5-Jul-09 20:12

Sasmi_Office

5-Jul-09 20:12

Dear Friend,

I am working on database which is having huge data and having complicated stored Procedure, some time i face problem because of this, want to clear all this....

To overtook this all problem i start working on Procedure which is making issue and fine tune some extend, i want to change them just like blood vain in my body so data can be flow just like the blood but this can only happen if i know the imp things....

You got idea what i want, i want to know the exact process for all this.

Me using following characters and syntex in my queries
1) IN and NOT IN
2) <>
3) CASE
4) GROUP BY.
5) Using Date Converting it to nvarchar datatype (for view only not in where clause).
6) UNION and UNION ALL
7) SUB Query
8) INNER JOINS (other joints are use but lesser amount)

Read some where that it is not good to use following character in where clause
1) IN and NOT IN
2) <>
3) CASE

I am using all this which is not possible to not use all this, if you have any other idea please give it to me.

Give me some link some article which help for all this

thanks and regards in advance,

Sasmi

Re: Want to understand how to fine tune stored procedure in SQL Server 2005?

Blue_Boy5-Jul-09 22:10

Time Difference w.r.t Dates

5-Jul-09 22:10

Check this.[^]

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.

AliAmjad5-Jul-09 19:37

AliAmjad

5-Jul-09 19:37

Actually i just want to output the difference of two Timespans with respect
to its dates e.g.

07/03/2009 05:00:00
07/04/2009 10:00:00

and the difference i get is 05:00:00 which is not correct i am using the following code:

DECLARE @EnterTime datetime, @DischargeTime datetime

Select @EnterTime = convert(varchar,EnterTime,8) from visit

Select @DischargeTime = convert(varchar,NextTime,8) from visit

Select convert(varchar,@DischargeTime - @EnterTime,8)

the above code is working fine if the duration is between 24 hours. Can you please suggest me a solution or i have to apply the logic on programmatic side i.e. .NET?

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

Blue_Boy5-Jul-09 20:41

5-Jul-09 20:41

Do you want to get days or hours or minutes between two dates?

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.

AliAmjad5-Jul-09 21:01

AliAmjad

5-Jul-09 21:01

I want to get time in HH:mm:ss format e.g. 13:15:02

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

Blue_Boy5-Jul-09 21:39

5-Jul-09 21:39

This will do your trick.

DECLARE @d1 DATETIME, @d2 DATETIME, @sd INT 

SET @d1 = '20090706 00:00:00' 
SET @d2 = '20090707 01:30:01' 

SET @sd = DATEDIFF(SECOND, @d1, @d2) 

SELECT  
CASE WHEN @sd/3600<10 THEN '0' ELSE '' END 
+ RTRIM(@sd/3600) 
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) / 60),2) 
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) % 60),2) AS Hours

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.

AliAmjad5-Jul-09 21:54

AliAmjad

5-Jul-09 21:54

Thanks Bro... Smile | :)

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

Blue_Boy5-Jul-09 22:01

5-Jul-09 22:01

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.

BFILE, BLOB?? [modified]

Muammar©4-Jul-09 7:56

Muammar©

4-Jul-09 7:56

Hey everyone,
In Oracle, can we store files as attachments in a table field "Files like MS. Word, Excel, PDFs" so it contains all the files embedded in a table and when exporting the database, you dont need to also backup a folder of files from your drive as well.

Please advise.
Many thanks!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

modified on Sunday, July 5, 2009 6:46 AM

Niladri_Biswas4-Jul-09 18:00

Niladri_Biswas

4-Jul-09 18:00

Yes it can be done.

If you are using Sql Server 2008, you can take the advantage of the new feature FILESTREAM Storage.
http://msdn.microsoft.com/en-us/library/cc949109.aspx[^]

If you are using Sql Server 2005 store it in native SQL Server blob type.

Here's a nice introduction
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1273934,00.html[^]
to blob types in SQL Server by Denny Cherry.

Here's an example of writing blobs using C#.
http://www.akadia.com/services/dotnet_read_write_blob.html[^]

Hope this helps.
Smile | :)

Niladri Biswas

Muammar©

5-Jul-09 0:40

Thanks mate but I'm sorry I forgot to mention that I'm using Oracle and apparently whatever the case is, I still need to store external files on the drive which's mainly my question.. Thanks anyways mate.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

ScottM16-Jul-09 8:46

ScottM1

6-Jul-09 8:46

Try this[^]

Muammar©

6-Jul-09 10:31

Thanks!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Top N Per StockCode

Elizma4-Jul-09 3:27

Elizma

4-Jul-09 3:27

Hi Clever People.

I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way.

I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode.

Can anyone give me advice please on a quick and painless way or should I just go with the cursor?

Kind Regards

Elizma

Niladri_Biswas4-Jul-09 3:43

Niladri_Biswas

4-Jul-09 3:43

Hope you have a table(tblStock) with a column like StockCode

StockCode
--------
1
2
3
4
5
6
7
8
9
10

The query is:

SELECT TOP 5 StockCode FROM TBLStockCode

Hope this helps
Smile | :)

Niladri Biswas

Elizma5-Jul-09 6:45

Elizma

5-Jul-09 6:45

Hi
Sorry. Thanx for your supply, maybe I didn't ask the question properly. I now the plain top 5 query. My problem is:

I have a master StockCode table that will contain unique StockCodes and their information.

I also have a Details Table that is linked to the Master StockCode Table. Each Stock Code in the Master might have 20+ rows in the Detail table. I want to specify the Top N PER StockCode.

E.g. If we use only TOP 2

StockCode1 - DetailDataRow1
DetailDataRow2
StockCode2 - DetailDataRow1
DetailDataRow2

Not maybe in this format, but I still need to get the TOP 2 rows per StockCode and not the First Top 2 Rows like what the normal solution that you suggested would do.

Hope this is a bit more clear.

Thanx for trying to help.

Elizma

Isaac Gordon5-Jul-09 22:01

Isaac Gordon

5-Jul-09 22:01

I am Not getting your Question pls clearly define it.
use ranking Function like Dense_Rank() of SQL Server.

Dinesh