Hot answers tagged sql
7
The recent 5.6 version has added this feature.
See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer
Typical Usage:
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET ...
4
This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.
mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema ...
4
The short answer: you cannot add column to a table at a specific position.
When you add new columns they always go after the existing columns. You have to re-create your table with new definition to place a column to a position you need.
You can however create views with columns positioned arbitrarily based on your existing table, and then query or update ...
3
Yes, you'll need to recreate the package in 2008. 2005/2008 packages can be upgraded to 2012, but downgrading doesn't work.
There is a codeplex project/utility that can move 2008 packages to 2005 by changing version numbers, and making small changes to the XML. Unfortunately this approach doesn't work with 2012, as the package format went through some big ...
3
Two thoughts come to mind.
If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.
You could do the update, query the data, and if it all looks okay, Commit the Transaction. If it fails, you could perform a Rollback.
Alternative
You may want to look at ...
3
Using SELECT * is bad practice especially in a stored procedure. Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.
As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied. However I would have a clustered index on ID and 1 non ...
2
You are doing the right way,
In many-to-many relation your must have a third table mapped by the ID of the two other tables. The two Id must be foreign key, and the association of the two is the primary key of your association's table
You can also store in this table the information that are related on the relation.
2
As per the code in your comment:
USE msdb
EXEC sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients = 'vince.chan@ufa.com',
@subject = 'T-SQL Query Result',
@body = 'The result from SELECT is appended below.',
@query = '(include script from above)'
The reason this won't work is the same reason why this wouldn't work:
declare ...
2
Solution 1:
Make the event_id nullable.
ALTER TABLE events
MODIFY COLUMN event_id int unsigned NULL ;
This will allow you to insert rows which reference nothing (NULL):
INSERT INTO events
(user_id, event_id, title)
VALUES
(a_valid_user_id, NULL, 'test event 1') ;
And to see events without a "parent" event:
SELECT *
FROM events
WHERE event_id ...
2
As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.
Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step ...
1
The best I can immediately mention is to simply run EXPLAIN on a query.
There is a graphical way to do this
pt-visual-explain
mk-visual-explain (Source Code)
As for steps to optimizing a query, a parse tree is created. Rather than explaining the parser here, please read my post from Mar 11, 2013 (Is there an execution difference between a JOIN condition ...
1
Your best option (considering your limitations) is probably using OPENROWSET with a FORMAT FILE
Take a look at those links:
Use a Format File to Bulk Import Data
You can use the BCP utility once locally to Create a Format File
I prefer the XML Format Files
1
Your WHERE clause must be after your join, perhaps?
SELECT *
FROM CallDetailRecord
JOIN CallEvent ON EventID = CallEventID
WHERE StartTime >= 1357102799000 AND StartTime <=1357880399000
Note: You may want to prefix the fields with table names or aliases...
1
@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).
But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline ...
1
You can send HTML email using below code :
/*********************************************************************
Author : Kin
Date : 5/16/2013
Tested RDBMS: SQL Server 2005 and up .. for dba.stackexchange.com
Purpose : Send HTML Report
**********************************************************************/
declare @tableHTML ...
1
Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:
UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')
concat_ws() ignores NULL values. With plain ...
1
The "Best Way" is probably to do it the former way, calculating the accurate count of messages and friends, etc is going to be easier to manage on the application side. Performance will obviously depend on how many messages and friends, some variables on the database server, and indexes.
A third solution would be to use a caching layer (like memcached) to ...
1
Since you are trying to avoid SSIS and BCP: If you don't have a deep-seated hatred of MS Access, you might want to take a brief look at it. It has a pretty slick flat-file import wizard. I prefer it over SSIS for small one-time (manual) jobs with flat-files. MSAccess can link directly into SQL Server (or equiv) tables and import directly into the server ...
Only top voted, non community-wiki answers of a minimum length are eligible
