Showing posts with label bear. Show all posts
Showing posts with label bear. Show all posts

Sunday, March 25, 2012

Best program for SQL database manipulation

Hello All

I am a relative beginner to SQL databases & new to this forum, so please bear with me if my query is too basic and advise if this question belongs somewhere else

I began working at a company that uses a program that stores data in an SQL database running off a Firebird engine

The program itself doesnt come with database management/administration module, so I'll need to use an external program for manipulating data in the tables that the database contains.

I have relatively little knowledge in SQL programming, which is why I would like to know which is the most powerful program for SQL database updating / manipulation?

This database has tables that has an infinite number of joins with other tables - Even MS Access wasnt able to open a few tables in this database because of the number of joins. I have tried Access & Lotus Approach, Approach manages to do a better job than Access, it open the tables & seems like it will let me import external data directly into the SQL table, but takes forever & usually just bums out giving an error after a very long wait..

My question is - Apart from Access & Approach, are there any more powerful, yet user friendly programs out there that can help me update data directly into SQL tables? What options do I have - the tasks I need to perform are pretty simple updating & cleaning of data already in there

Please, I will hugely appreciate any pointers that you guys the experts might have for me in this regard

Thanks
AlexTry Microsoft SQL Server 2005 Express. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/sql

Monday, March 19, 2012

Best Practice for this SP Scenario !

Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !

* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)

In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...

There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :

if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
-----------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)

3) it could be done (maybe) using Functions in SQL SERVER...

There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario .... !!!

I'll appreciate your help ...
Thanks in Advance . ! .Sql2k recompiles the entire sp if a recompilation is needed. Thus, it's best to split up the sproc into child sprocs. So, your #2 would be the way to go.

I suggest you read up on this excellent article.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx