Database

Re: Create composite key on existing table

30-Jun-06 10:22

Not quite - You would use ALTER TABLE[^]. Something like this:

ALTER TABLE Descriptions
ADD CONSTRAINT PK_Descriptions PRIMARY KEY CLUSTERED (PartID, DescriptionPartID)

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

leckey30-Jun-06 10:31

IBM DB2 SQL Error Code to String

30-Jun-06 10:31

As always, Thanks man! Big Grin | :-D

Christopher Stratmann30-Jun-06 2:55

Christopher Stratmann

30-Jun-06 2:55

In DB2 sometimes error messages are generated and returned in the sqlca.sqlcode variable in C++. To find out the string representation of this number I have to go to http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2m0/sql0000.htm[^]. Is there function to return the error string represented by this number so I dont have to go to this web site each time.

I have been researching this for sometime and I found a C++ APIs on this web site http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/ad/rwrutl01.htm[^]. I didnt find any function to do this but it might help.

Another thing. If this "method" could be language specific that would be great too (meaning if English is defined return an english string or if German is defined return the german string).

Thanks Chris

Laoding the whole database tables into a DataSet

hamidreza_buddy30-Jun-06 0:43

hamidreza_buddy

30-Jun-06 0:43

hi guys
how should I load a whole database into a Dataset object.
when loading from a DataAdapter i should use an SQL command. what if i want to load all tabless and reltions and ... in a Dataset?
i want to make a program to load all sort of databases (even without knowing the name of the tables)
thx guys

Re: Laoding the whole database tables into a DataSet [modified]

Colin Angus Mackay30-Jun-06 1:02

Re: Laoding the whole database tables into a DataSet

30-Jun-06 1:02

hamidreza_buddy wrote:
how should I load a whole database into a Dataset object.

Hopefully it is a small database. The last database I worked on was 150Gb when I left the project and growing at roughly 1Gb per week. That would never have fit in a DataSet.

The other question is: Why would you want to?

hamidreza_buddy wrote:
when loading from a DataAdapter i should use an SQL command. what if i want to load all tabless and reltions and ... in a Dataset?

Your SqlCommand would consist of:

SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC; -- etc...

hamidreza_buddy wrote:
i want to make a program to load all sort of databases (even without knowing the name of the tables)

You need to know the names of the tables to put in the SqlCommand. However, you can get this information:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

hamidreza_buddy1-Jul-06 0:53

hamidreza_buddy

1-Jul-06 0:53

thx
we want to make a Data Mining Application.
unfortunately i am a newbee in databases and i dont know the strategies for making such an application.
i really need ur suggestions.
how should i do proccess on databases.
* should i store them in a DataSet or DataReader or what?
as i said it should load every database without knowing name of tables.
waiting for ur strategies.
thx again

Re: Laoding the whole database tables into a DataSet

Colin Angus Mackay1-Jul-06 1:15

Please help me for finding a word from a field of database

1-Jul-06 1:15

hamidreza_buddy wrote:
we want to make a Data Mining Application.

Then leave the data in the database and only ever get what you need at that moment.

Data mining applications usually deal with vast quantities of data, much more than the RAM+swap space of a server, let alone a regular desktop.

Database engines are optimised to do vastly complex "set based" calculations on the data in a database. A DataSet can manage only small fraction of this. DataSets a bebeficial when a small amount of data is repeatedly queried - in this case the performance of the dataset out weighs the round trip cost of performing the query on the server.

Set based operations are those which involve multiple rows of data (a set of data). They are expecially good at joining sets of data together. (If you want to delve really deep into this area they you might like to read up on set theory in mathematics)

hamidreza_buddy wrote:
how should i do proccess on databases

Let the database do the work and you pull across only the answers.

hamidreza_buddy wrote:
should i store them in a DataSet or DataReader or what?

A DataReader is not a storage mechanism. A DataReader is a mechanism for reading data (hence the name DataReader) from a database.

I, personally, don't like DataSets because they are bulky and I can get better results by creating my own business objects. In .NET 2.0 the typed datasets are supposed to be much better, but I've not yet looked at them.

hamidreza_buddy wrote:
i said it should load every database without knowing name of tables.

I already gave you the query you should use to obtain a list of table names. You need to know the name of the table in order to refer to it.

hamidreza_buddy wrote:
waiting for ur strategies

I have a pathological hatred for the use or "ur" the word is "your". The same goes for "thx" - THX is a sound quality assurance system that means that if you see a movie in a THX certified theater it will sound exactly as it did in the mixing studio.

On the subject of strategies - There are lots out there, but for a data mining application I don't thing a DataSet will feature much for the mining aspect. Maybe only for helping you bind data to the controls (should you wish to go that route)

Jesal Rana29-Jun-06 23:22

Jesal Rana

29-Jun-06 23:22

Hi,
Please any one can help me for how I find a word from a paricular field from database in SQL

Please reply urgently.

Regards,

Jesal Rana

OK

Re: Please help me for finding a word from a field of database

Paddy Boyd29-Jun-06 23:35

Paddy Boyd

29-Jun-06 23:35

select a from table where a like '%word%'

Or set up a full text index and use the CONTAINS predicate.

substring and charindex

uglyeyes29-Jun-06 15:23

uglyeyes

29-Jun-06 15:23

Hi My one column field is something like

1.1.1.1

please note it can be any number between 1 to 1000 before each .
e.g. upto 1000.1000.1000.1000

1. my question is how can i extract,
anything that starts before second '.' and insert into another column with in a same record.

2. how can i extract any number that starts after second '.' and before third '.'

please provide me clear direction

cheers.

Re: substring and charindex

Colin Angus Mackay29-Jun-06 21:36

Re: substring and charindex

29-Jun-06 21:36

This breaks the first normal form. You should insert the data into the database in the split form in the first place. You can then create a computed column that concatenates the strings back together again.

However, if you are insistant on your approach above you might find these string functions[^] useful.

As for inserting the data into other columns. You can either do this when you create the row in the first place using INSERT[^] or after the row is initially created using UPDATE[^]

vivek-g30-Jun-06 1:15

vivek-g

30-Jun-06 1:15

hi,

try this out ...
I am giving you the code to fragment the column field as you have described.
Try the code with diffrent strings like '1.1.1.1' ,'1.891.91.21' etc.

I am taking '1000.087.1567.7956' as example.
<----------------------------------->

DECLARE @vStr AS VARCHAR(100);
SET @vStr='1000.087.1567.7956'
--first no.
SELECT SUBSTRING(@vStr,1,CHARINDEX('.', @vStr) - 1)as A

--second no.
SELECT SUBSTRING(@vStr, CHARINDEX('.', @vStr)+1 ,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )-(CHARINDEX('.', @vStr)+1)) as B

--third no.
SELECT SUBSTRING(@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1 ,CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)- (CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)) as C

--fourth no.
SELECT SUBSTRING(@vStr,CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)+1,LEN(@vStr)+1-(CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)+1)) as D

<-------------------------------->

kindly do feel free to ask if you have doubt about it.....

vivek
delhi

vivek

Re: substring and charindex

Mike Dimmick1-Jul-06 21:34

Mike Dimmick

1-Jul-06 21:34

To back Colin's comments up - we wrote and support a system where it was originally not anticipated that the parts of a certain identifier (carton number, IIRC) would need to be queried for a certain table. A changed requirement made this necessary. The substring operations make it impossible for SQL Server to use an index, so every row must be accessed, which you almost never want to happen unless your table is very small.

Further, something about the query sometimes causes SQL Server to pick a poor execution plan which takes an unacceptable amount of time to complete. Often running sp_updatestats fixes the problem, but I think this is simply because it forces the query to be recompiled.

We're looking at adding extra columns to sort this out.

In terms of what you need to do - preprocess your data before insertion. Almost any client-side language (C#, VB.NET, etc) is far better at string manipulation than SQL is. If you already have data that's in the format you've shown, then doing a one-time update is acceptable.

Stability. What an interesting concept. -- Chris Maunder

Best practices for installation needed

Gary Wheeler29-Jun-06 11:04

Gary Wheeler

29-Jun-06 11:04

I'm building an application that uses ODBC. I'm using SQL Server 2005 Express for my test bed, but I would like to be portable to the customer's DBMS if possible.

What are the 'best practices' for creating an empty data base?

Based on what I've read, I'm guessing that, since data base creation is a privileged activity, that I will have to have the user create the data base manually. I'm thinking that, at that point, my application could go ahead and create the empty tables if they aren't present when I open the data base.

The alternative would be to have some kind of SQL script run by the installer to create the empty tables.

Anyone have recommendations or suggestions?

Software Zen: delete this;

Re: Best practices for installation needed

Frank Kerrigan30-Jun-06 6:19

Frank Kerrigan

30-Jun-06 6:19

Create a SQL script that can create db and then create all tables etc.

In 2005 Express. Right Click your db -> Tasks -> Generate Scripts...

This will take you to the script Wizard where you can script your entire database.

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: Best practices for installation needed

Gary Wheeler30-Jun-06 7:54

Gary Wheeler

30-Jun-06 7:54

Thanks for the response.

Frank Kerrigan wrote:
In 2005 Express. Right Click your db -> Tasks -> Generate Scripts...

Yeah, I've got that, and will use it for the 'default' installation case where I'm installing SQL Server 2005 Express.

I would like to provide a 'sanitized' version of that script for creating the data base under other DBMS's (like Oracle, MySQL, etc.). I've not found anything specific on MSDN that describes which parts of their script are not ANSI-SQL, which seems to be a sort of a common denominator.

Software Zen: delete this;

Instead of Trigger for SQL Server Column Question

Ambsgirl29-Jun-06 10:06

Ambsgirl

29-Jun-06 10:06

I need to analyze one of the column values during an insert action, alter it and have it insert into the database and I figure an Instead of trigger is the best way to go. But, the table structure that my instead of trigger is on can change so I do not want to hard-code the column names in my trigger. How do I select from inserted and loop through the columns so that I can create another insert statement with those column names and values?

Copy column from one table to another question

leckey29-Jun-06 9:11

29-Jun-06 9:11

Okay, this is a question for a coworker.
Table A has two fields: partNumber, weight
Table B has two fields: partNumber weight

Table A has about 1000 records, table B has about 10,000 records.
Table A has the most recent information about weight. What he wants to do is if the part number in table A also exists in table B, update that weight with the weight from table A. (If the part number exists in table b, the weight is outdated.)

How can we do this in sql?

Thanks all!

Colin Angus Mackay29-Jun-06 9:30

29-Jun-06 9:30

leckey wrote:
Table A has the most recent information about weight. What he wants to do is if the part number in table A also exists in table B, update that weight with the weight from table A.

UPDATE B
SET B.weight = A.weight
FROM A
INNER JOIN B ON A.partNumber = B.partNumber

leckey wrote:
If the part number exists in table b, the weight is outdated

Did you want to do anything in that case, or just ignore the outdated stuff.

E&OE

leckey29-Jun-06 9:32

29-Jun-06 9:32

Basically ignore any records in table B (with a weight) that doesn't have a corresponding record in table A.

Thanks Colin!

leckey29-Jun-06 9:36

29-Jun-06 9:36

I told the guy this is for that you are from Scotland. He went on to tell me a story that at a previous job a Scotsman (sp?) Smile | :)

and another guy apparently got upset over some dresscode changes, so they showed up at work wearing kilts for a week or so.

Colin Angus Mackay29-Jun-06 9:41

29-Jun-06 9:41

leckey wrote:
Scotsman (sp?)

That's correct.

leckey wrote:
so they showed up at work wearing kilts for a week or so.

It is too warm in my office to wear kilts. The aircon broke and the temperature at my desk is ~30ºC so having 8 metres of wool wrapped around my waist isn't the best idea.

leckey29-Jun-06 9:43

29-Jun-06 9:43

I pave ya driveway! Big Grin | :-D

Colin Angus Mackay29-Jun-06 9:56

29-Jun-06 9:56

leckey wrote:
I pave ya driveway!

But my driveway is made of Tarmac

leckey29-Jun-06 10:00