sql

Is this a good or bad way of generating random numbers for each record?

A colleague of mine discovered a behaviour in SQL Server which I was unaware of. CREATE VIEW dbo.vRandNumber AS SELECT RAND() as RandNumber GO CREATE FUNCTION dbo.RandNumber() RETURNS float AS RETURN (SELECT RandNumber FROM vRandNumber) GO DECLARE @mytable TABLE (id INT) INSERT INTO @mytable SELECT 1 INSERT INTO @mytable SELECT 2 INSE...

Select * from table OR select id,field1, field2, field3 from table - best practice?

Could someone chime in as to whats a better practice? for select queries should I return all or the IDs that I require? Efficiency? Scalability? etc. thanks Env: SQL Server 2008, VS2008 (VB) ...

Array variable in mysql

Is there a way in a mysql script to declare an array(or any collection) and looping over it for doing stuff. Ex : SET @myArrayOfValue=[2,5,2,23,6] for each @value in @myArrayOfValue INSERT INTO EXEMPLE VALUES(@value, 'hello'); end for each Thanx in advance. ...

Problem with SQL Merge Statement

Source Table Id, Name, Address 1 A #202 1 A #203 1 A #204 2 A #202 Target Table Id, Name, Address 1 A NULL After Merge Id, Name, Address 1 A #202 2 A #202 I am using this SQL create table #S (ID int, Name varchar(25) NULL, Address varchar(25) NULL) create table #T (ID int, Name varc...

SCOPE_IDENTITY And Instead of Insert Trigger work-around

OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert...

What do you do in SQL Server to CREATE OR ALTER?

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead. IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog') CREATE PROCEDURE dbo.Syn...

[MySQL] How to insert additional values in between a GROUP BY

Hi, i am currently making a monthly report using MySQL. I have a table named "monthly" that looks something like this: id | date | amount 10 | 2009-12-01 22:10:08 | 7 9 | 2009-11-01 22:10:08 | 78 8 | 2009-10-01 23:10:08 | 5 7 | 2009-07-01 21:10:08 | 54 6 | 2009-03-01 04:10:08 | 3 5 | 2009-02-01...

SQL Server equivalent to MySQL enum data type?

I have been trying to find out if the data-type enum exists in SQL Server 2008 like the one you have in MySQL. ...

What's wrong with output parameters?

Both in SQL and C#, I've never really liked output parameters. I never passed parameters ByRef in VB6, either. Something about counting on side effects to get something done just bothers me. I know they're a way around not being able to return multiple results from a function, but a rowset in SQL or a complex datatype in C# and VB work ...

Exporting a CLOB to a text file using sqlDeveloper

I am using sqlDeveloper and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal is to not use Oracle here (I received an Oracle dump - which was loaded into an oracle db, but am u...

Migrate MS Access to MySQL

I'm getting data as an Access file. My application uses MySQL/Java. I'd like to parse the Access data, and stick it in MySQL. Is there a Java tool that will help me do this? ...

[SQL Server] Valid UPDATE, INSERT syntax?

Are these statements valid? UPDATE Table1 FROM (SELECT * FROM Table2) INSERT INTO Table1 (SELECT * FROM Table2) ...

Exclude set from left outer join

How do you exclude a set of values when using a left outer join? Consider the following query: SELECT i.id, i.location, area.description FROM incident_vw i, area_vw area WHERE i.area_code = area.code(+) AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P') The query executes, yet none of the NULL area code...

A subquery that should be independent is not. Why?

I have a table files with files and a table reades with read accesses to these files. In the table reades there is a column file_id where refers to the respective column in files. Now I would like to list all files which have not been accessed and tried this: SELECT * FROM files WHERE file_id NOT IN (SELECT file_id FROM reades) This ...

Cross Tab - Storing different dates (Meeting1, Meeting2, Meeting 3 etc) in the same column

I need to keep track of different dates (dynamic). So for a specific Task you could have X number of dates to track (for example DDR1 meeting date, DDR2 meeting date, Due Date, etc). My strategy was to create one table (DateTypeID, DateDescription) which would store the description of each date. Then I could create the main table (ID, ...

how to speed up Mysql and PHP?

Hello I am developing a script in my localhst using PHP and mysql and I am dealing with large data (about 2 millions of records for scintific research) some queries I need to call once in a life (to analyse the data and prepare some data); however it takes very long time for example: now my script is analysing some data for more than 4...

mysql Stored Procedure not releasing lock

I have a store procedure that gets a lock, runs a select, does some simple processing and runs an insert. This has worked fine for over a year but today every so often a connection will hold the lock and not release it until the lock times out. So if i use the select IS_USED_LOCK('up_XML_insertUIAudit_lock'); I can determine what c...

Timestamp updating in table with REPLACE INTO

I have a plus/minus system where a user can add one or minus one away from a blog post. My database (MySQL) table looks like this: userid entry id vote - which is +1 or -1 timeStamp I have set the timeStamp to default to CURRENT_TIMESTAMP. I have to methods plus() and minus(). Both do the same thing but one inserts +1 into 'vote' and...

Slow SQL update could use some help.

An ex-coworker of mine wrote the following UPDATE as part of a data import script and it takes nearly 15 minutes to complete on a table of 92k rows. UPDATE table SET name = ( SELECT TOP 1 old_name FROM ( SELECT SUM(r) rev, number, name, intermediate_number, interm...

Slow MySQL retreival of the row with the largest value in indexed column

I have a SQL table readings something like: id int client_id int device_id int unique index(client_id, device_id) I do not understand why the following query is so slow: SELECT client_id FROM `readings` WHERE device_id = 10 ORDER BY client_id DESC LIMIT 1 My understanding with the index is that mysql keeps an ordered list (one prop...