Using SELECT to INSERT records in SQL
In MS SQL, very often I want to insert records into a table based on the record set of another query I have.
I always end up looking up this over and over, so here it is for your reference and mine.
INSERT table_desitination
(td_field1, td_field2) SELECT ts_field1, ts_field2 FROM table_source
ORACLE FOUNDATION: User Responsibilities
One of the reoccurring tasks of an Oracle developer, is having to continually check what a users’ oracle responsibility is, or if they have multiple, are. There are multiple ways to obtain this information.
You could ask the user, but often, they do not use every responsibility assigned to them and the information you get from any user, as a rule, needs to be validated.
You could request this information from a business analyst. This is viable, but then you are on the analysts time. The object here is to accomplish as much as you can with as little involvement as possible from outside of the development group.
The following query can be used to pull all oracle users and their associated responsibilities. It validates that the user is a current user and that the responsibility is both enabled and hasn’t been end dated for that user.
SELECT u.user_name, responsibility_name responsibility, a.start_date
FROM apps.fnd_user_resp_groups_all a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL
and u.user_id = a.user_id
order by responsibility_name
Of course if you need to filter or are looking for specific user(s) or responsibility(ies), you can add condition statements to refine the query.
If you find that this is a repeated task, you can create a view out of the query and run as needed, or base a report from the view to have UI and standard run process. To turn this query into a view, execute the following:
Create or replace view xxfnd_user_responsibilitites as(
SELECT u.user_name, responsibility_name responsibility, a.start_date
FROM apps.fnd_user_resp_groups_all a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL
and u.user_id = a.user_id
order by responsibility_name)
Metadata Monday: SQL Server 2012 and Data Explorer
Microsoft’s Data Explorer is a a touch-sensitive visualization tool that will orbit around the new SQL Server ecosystem. Admittedly, Microsoft Excel has long been the tool used to manipulate and visualize data, but even Quentin Clark, Microsoft’s own corporate vice president for database systems, tells RWW in this article that using Excel for enterprise data is not really optimal.
Why? Because people tend to share Excel spreadsheet fragments, other people make changes, and the data eventually falls out of context with the database itself.Here is what Clark says specifically:
“It’s not so much that we don’t want Excel in the loop,” says Clark about a product his team doesn’t manage. “We want to give IT tools to make data sharable, so [people] can stop using Excel as the way data and information gets flowed, over e-mail… [and] ensure that SharePoint can be used as a sharing and collaboration mechanism, as opposed to e-mail.”
And that’s just the icing on the cake. Data Explorer allows sharing data through SharePoint too.
SELECT (JOIN/ON) -> UPDATE (FROM/WHERE)
Ever wanted to perform an SQL update but needed to join on another table?
I recently learned how to do this, again, because I forgot the first time. I kept trying to use JOIN within the UPDATE and getting things either not working how I wanted or not working at all (which I guess are really the same thing fundamentally).
So I’m blogging here in case I forget again.
I try to think of it as ‘UPDATE uses FROM and WHERE like SELECT uses JOIN and ON’:
SELECT coulmn_name FROM table_1 JOIN table_2 ON table_2.table_1_id = table_1.id;
UPDATE table_1 SET coulmn_name FROM table_2 WHERE table_2.table_1_id = table_1.id;
Any other WHERE conditions for the UPDATE can just be added on the end.
M
SQL Course
sqlcourse.comThis link provides a short course on SQL from the basics to advanced queries. If you are already familiar with SQL, it could be useful for reference.
Using Fulltext Index search in MySQL
databasejournal.comOne of the more useful MySQL features is the ability to search for text using a FULLTEXT index. Currently this is only available if you use the MyISAM table type (which is the default table type, so if you don’t know what table type you’re using, it’ll most likely be MyISAM). A fulltext index can be created for a TEXT, CHAR or VARCHAR type field, or combination of fields. We’re going to create a sample table and use it to explore the various features.
The simple form of usage (the MATCH() function) is available to all MySQL servers from version 3.23.23, while the more complex usage (the IN BOOLEAN MODE modifier) is available from version 4. The first part of this article looks at the former, and the second part at the latter.
SQL Server Database | DBA-24x7
dba-24x7.comSQL Server Database: DBA-24x7 offers comprehensive solution for crisis ranging from database monitoring, applying proactive techniques.
Databases should just work with everything
When I am developing a program or website I often have to use databases in some or another. Sometimes each file is just an SQLite database file and sometimes I have one file for managing all the data a program uses. Sometimes my web applications may require many databases. Databases are generally the easiest way of loading/storing data and they just work.
But there is a problem with databases. They are an absolute nightmare, in most programming languages, to work with. In C/C++ you need to use SQLite which takes loads of coding to initialize. The same problem exists in Java. And .Net. And Python. And PHP. Not only do you have to write lots of code to work with them, you also have to do a lot of work to lay them out. Sometimes you can use visual designers, sometimes you have to use an SQL console.
I would like, one day, to be able to write one line of code to access a database: Database db = new Database(“C:\dbs\mydb.db”, “username”, “password”);. And then if I could run queries the way that I want to run them and if I could present data the way I like my life would be a lot easier. Perhaps I will code the described database class. I think that will be my ‘job for the week’.
Regularly Scheduled Maintenance Window Saturday Night / Sunday Morning
11:00 pm CDT 9/10/2011 - 1:00 am CDT 9/11/2011
Our regularly scheduled maintenance window will be used to update the structure for one of our SQL clusters and test the failover to prevent future outages from occurring. Customers on this cluster will experience intermittent service accessibility during this window.
If you have any further questions, please open a support ticket on the Fpweb.net customer portal: http://support.fpweb.net/
Thank you,
Fpweb.net Support
Update 11:55PM CDT- This maintenance window has closed. If you experience any further issues, please submit a support request at http://support.fpweb.net. Thank you, Fpweb.net Support Team
Centralized Auditing, Data Aggregation, Deployment Processing
I have been experimenting with SERVICE BROKER, XML, XPATH, & MERGE for a way to transfer data across untrusted domains. We have many uses for a reliable and standardized way to move data, look-up data and trigger events. One example is a nightly config checker and health evaluation job that runs on well over 100 servers. This job populates about 8 tables in our DBA DB, such as DBA_DBServerInfo, DBA_DBInfo, DBA_DBDiskInfo…. I want this data aggregated at a central HUB SQL Server for look-ups, reporting, and alerting. I also want this system to be usable for other data gathering, data auditing, DDL auditing, data pushing, and deployment processing projects.
SSB was fairly simple to set up other than it is not very GUI centric. I was able to create certificates on the servers to authenticate with so that I can get past the Domain and sql login issues and simply open a single port between all SQL Servers to perform all DBA Processing messages. After I was sure I could get Service Broker to do what I wanted, the next step is to create code the reliable takes a table to XML and back to a table again.
The first hurdle was to encoding the data into XML in a way that was generic, contained all of the information of where it came from and how to turn it back into a table to put back into a table. “SELECT * FROM TABLE FOR XML RAW” gave me the data as XML but I found it extremely difficult to put it back into a table. I finally did come up with a great process now that works well and I also came up with a way that works with single tables or a collection of tables in a single XML Doc.
I added XMLSCHEMA to the query and it gave me all of the schema I needed to query the data back out of the XML to Merge it into the destination table. The next trick was to figure out how to get what I wanted out of the XSD portion of the XML so I could query the data portion back into the exact formats it came from.
This was my first experiment with merge and I also found it to be fairly handy. I was able to dynamically generate a merge statement that looks at the PK of the destination table and uses those columns to determine between an insert or an update.
There really isnt much information or examples (that work) out there for xpath queries so it took quite a while to get something that works. I am sure someone out here can tell me how bad I did and how much better it could be. which is exactly what I want.
This is my basic encode - decode - merge of a single table…
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb..#XMLSCHEMA’) IS NOT NULL DROP TABLE #XMLSCHEMA
IF OBJECT_ID(‘tempdb..##Source’) IS NOT NULL DROP TABLE ##Source
DECLARE @XML XML
,@XSL XML
,@TSQL nVarChar(4000)
,@TableName sysname
CREATE TABLE #XMLSCHEMA
([name] sysname NULL
,[use] sysname NULL
,[type] sysname NULL
,[base] sysname NULL
,[value1] sysname NULL
,[value2] sysname NULL)
————————————————————————————————————
————————————————————————————————————
— GENERATE A XML CHUNK TO PLAY WITH
————————————————————————————————————
————————————————————————————————————
SET @XML =
(SELECT * From dbaadmin.dbo.DBA_DBInfo FOR XML AUTO,XMLSCHEMA,ROOT(‘Table’))
SELECT @XML
————————————————————————————————————
————————————————————————————————————
—GET ONLY THE TOP XDS PORTION OF THE XML CHUNK
————————————————————————————————————
————————————————————————————————————
SELECT @XSL = x.query(‘.’)
FROM @XML.nodes(‘/Table/*[1]’) a(x)
— COULD NOT USE REPLACE FUNCTION AS IT DOES NOT
— WORK FOR LARGE TABLES WHERE XML IS LARGER THAN 8000
— EVEN THOUGH REPLACE DOES ACCEPT VARCHAR(MAX)
— WOULD LIKE TO FIND XQUERY VERSION OF REPLACE
— SELECT @XML = CAST(REPLACE(CAST(@XML AS VarChar(max)),CAST(@XSL AS VarChar(max)),”) AS XML)
SELECT @XML = (
SELECT CAST(STUFF (
CAST(@XML.query(‘*[1]/*’) AS VarChar(max))
,1
,CHARINDEX (
‘</xsd:schema>’
,CAST(@XML.query(‘*[1]/*’) AS VarChar(max))
)+12
,”
) AS XML)
FOR XML RAW (‘Table’)
)
————————————————————————————————————
————————————————————————————————————
— GET THE TABLE NAME
————————————————————————————————————
————————————————————————————————————
SELECT @TableName = a.x.value(‘*[2]/@name’,’sysname’)
FROM @XSL.nodes(‘*’) a(x)
SELECT @TableName [TableName]
————————————————————————————————————
————————————————————————————————————
— POPULATE THE XMLSCHEMA TEMP TABLE
————————————————————————————————————
————————————————————————————————————
INSERT INTO #XMLSCHEMA
SELECT a.x.value(‘@name’,’sysname’) [name]
,a.x.value(‘@use’,’sysname’) [use] — use=”required” for PK of Source
,a.x.value(‘@type’,’sysname’) [type]
,a.x.value(‘*[1]/*[1]/@base’,’sysname’) [base]
,a.x.value(‘*[1]/*[1]/*[1]/@value’,’sysname’) [value1]
,a.x.value(‘*[1]/*[1]/*[2]/@value’,’sysname’) [value2]
FROM @XSL.nodes(‘/*/*/*/*’) a(x)
————————————————————————————————————
————————————————————————————————————
— CREATE THE DYNAMIC SQL TO CREATE THE SOURCE TEMP TABLE FROM THE XSD
————————————————————————————————————
————————————————————————————————————
SET @TSQL = ‘SELECT ‘
SELECT @TSQL = @TSQL — START BUILDING NEXT LINE OF QUERY
+ ‘a.x.value(”@’+[name]+”’,”’
+REPLACE(COALESCE([type],[base]+’(‘+value1+COALESCE(‘,’+nullif(value2,”)+’)’,’)’)),’sqltypes:’,”)+”’) ‘
+ QUOTENAME([name])
+ CHAR(13) + CHAR(10) + ‘ ,’
FROM #XMLSCHEMA ColumnData
SET @TSQL = REPLACE (
@TSQL+’||’ —APPEND DOUBLE PIPE TO END SO I CAN IDENTIFY LAST COMMA
,’ ,||’ — REPLACE LINE WITH LAST COMMA WITH NEXT VALUE
,’INTO ##Source’+CHAR(13) + CHAR(10)+’FROM @XML.nodes(”/Table/*”) a(x)’
)
————————————————————————————————————
————————————————————————————————————
— CREATE THE SOURCE TEMP TABLE FROM THE XSD
————————————————————————————————————
————————————————————————————————————
EXEC sp_Executesql @TSQL,N’@XML XML’,@XML
————————————————————————————————————
————————————————————————————————————
— SHOW THE SOURCE DATA AFTER INSERTED INTO THE TEMP TABLE
————————————————————————————————————
————————————————————————————————————
SELECT * FROM ##Source
————————————————————————————————————
————————————————————————————————————
— CREATE THE DYNAMIC SQL TO GENERATE THE MERGE STATEMENT
————————————————————————————————————
————————————————————————————————————
— CREATE DESTINATION IF IT DOES NOT ALREADY EXIST
————————————————————————————————————
————————————————————————————————————
— CREATE THE DYNAMIC SQL TO GENERATE THE MERGE STATEMENT
————————————————————————————————————
————————————————————————————————————
— ONLY DO MERGE IF DESTINATION HAS A PRIMARY KEY
IF EXISTS (
SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO
ON SIK.[id] = SO.[id]
WHERE SIK.indid = 1
AND SO.ID = OBJECT_ID(@TableName)
)
BEGIN
SET @TSQL = ‘MERGE INTO ‘+@TableName+’ as Target’ + CHAR(13) + CHAR(10)
+ ‘USING ##Source as Source’ + CHAR(13) + CHAR(10)
+ ‘ON’ + CHAR(9)
SELECT @TSQL = @TSQL + ‘Target.[‘+[name]+’] = Source.[‘+[name]+’]’+CHAR(13)+CHAR(10)+’AND’+CHAR(9)
FROM syscolumns
WHERE [id] = OBJECT_ID(@TableName)
AND colid IN (
SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO
ON SIK.[id] = SO.[id]
WHERE SIK.indid = 1
AND SO.ID = OBJECT_ID(@TableName)
)
SET @TSQL = REPLACE(@TSQL+’||’,’AND’+CHAR(9)+’||’,CHAR(13)+CHAR(10)+’when matched then update set’+CHAR(13)+CHAR(10)+’Target.’)
SELECT @TSQL = @TSQL + ‘[‘+[name]+’]=Source.[‘+[name]+’]’+CHAR(13)+CHAR(10)+’,Target.’
FROM #XMLSCHEMA ColumnData
SET @TSQL = REPLACE(@TSQL+’||’,’,Target.||’,CHAR(13)+CHAR(10)+’when not matched then insert’+CHAR(13)+CHAR(10)+’(‘)
SELECT @TSQL = @TSQL + ‘[‘+[name]+’]’+CHAR(13)+CHAR(10)+’,’
FROM #XMLSCHEMA ColumnData
SET @TSQL = REPLACE(@TSQL+’||’,CHAR(13)+CHAR(10)+’,||’,’)’+CHAR(13)+CHAR(10)+’values’+CHAR(13)+CHAR(10)+’(Source.’)
SELECT @TSQL = @TSQL + ‘[‘+[name]+’]’+CHAR(13)+CHAR(10)+’,Source.’
FROM #XMLSCHEMA ColumnData
SET @TSQL = REPLACE(@TSQL+’||’,CHAR(13)+CHAR(10)+’,Source.||’,’);’)
————————————————————————————————————
————————————————————————————————————
— SHOW THE MERGE STATEMENT
————————————————————————————————————
————————————————————————————————————
PRINT (@TSQL)
————————————————————————————————————
————————————————————————————————————
— RUN THE MERGE
————————————————————————————————————
————————————————————————————————————
EXEC (@TSQL)
END
————————————————————————————————————
————————————————————————————————————
— CLEAN UP TEMP TABLES
————————————————————————————————————
————————————————————————————————————
IF OBJECT_ID(‘tempdb..#XMLSCHEMA’) IS NOT NULL DROP TABLE #XMLSCHEMA
IF OBJECT_ID(‘tempdb..##Source’) IS NOT NULL DROP TABLE ##Source
Active Record Associations in the PHPonTRAX framework.
Active Record Table Associations belongs_to / has_oneBoth express a 1-1 relationship, the difference is mostly where to place the foreign key, which goes on the table for the class saying belongs_to. For belongs_to the foreign key from another table is in this table. For has_one there is a foreign key in another table to this table. Association names should be singular for both assoiation types.
Example:
class Post extends ActiveRecord {
public $has_one = "author"; # <= singular name
}
class Author extends ActiveRecord {
public $belongs_to = "post"; # <= singular name
}
The tables for these classes could look something like:
CREATE TABLE posts (
id int(11) NOT NULL auto_increment,
title varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE authors (
id int(11) NOT NULL auto_increment,
post_id int(11) default NULL,
name varchar default NULL,
PRIMARY KEY (id)
)
has_many
This expresses a 1-M relationship. There should be a foreign key in another table to this table where there are many records in that table linking to this table. Association names should be plural.
Example:
class User extends ActiveRecord {
public $has_many = "notes"; # <= plural name
}
class Note extends ActiveRecord {
public $belongs_to = "user"; # <= singular name
}
The tables for these classes could look something like:
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
fist_name varchar default NULL,
last_name varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE notes (
id int(11) NOT NULL auto_increment,
user_id int(11) default NULL,
note_text text default NULL,
PRIMARY KEY (id)
)
has_and_belongs_to_many ( habtm )
This expresses a M-M relationship. There should be three tables. Two tables with a third joining table. Association names should be plural. The joining table name must be named table1_table2, plural names for both in alphabetical order.
Example:
class Product extends ActiveRecord {
public $has_and_belongs_to_many = "categories"; # <= plural name
}
class Category extends ActiveRecord {
public $has_and_belongs_to_many = "products"; # <= plural name
}
The tables for these classes could look something like:
CREATE TABLE products (
id int(11) NOT NULL auto_increment,
sku varchar default NULL,
name varchar default NULL,
price DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories (
id int(11) NOT NULL auto_increment,
name varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories_products (
category_id int(11) NOT NULL auto_increment,
product_id int(11) default NOT NULL
)
Notes
If you need to define more that one of any of the above associations in the same model class, you can do the following:
public $belongs_to = "assoc1,assoc2,assoc3,etc....";
If you need to specify additional parameters with the association beside just the name you will have to define it as follows:
public $belongs_to = array("assoc1" => array("foreign_key"=> "weird_key"),
"assoc2" => null,
"assoc3" => array("conditions" => "age > 25"),
etc ...
); Visual Representation of SQL Joins
codeproject.comAlways a useful resource for SQL developers, here is a great post that visually demonstrates the various SQL joins and how you can use them to return just the rows you want.
Task Scheduler és MySQL backup
A feladatunk egy Windows MySQL szerveren kell futtatnunk automatikus adatbázismentéseket. Ehhez a beépített (Windows 7) Task Scheduler-jét fogjuk felhasználni.
A feladathoz továbbá felhasználjuk a MySQL Administratort.
FONTOS:
- Windows 7-es felhasználónevünknek egyeznie kell egy, a MySQL adatbázisunkhoz adott felhasználónévvel. Az adatbázis felhasználójának pedig jogosultságok kellenek, hogy készíthessen adatbázismentést.
- A jelszavaknak is egyeznie kell!
(Érdemes külön felhasználót nyitni. Mind a számítógéphez, mind az adatbázishoz. Ezzel jobban nyomon is lehet követni, hogy mikor és hogyan fut a backup.
Mivel nekem több adatbázisról kell egyszerre adatbázismentést készítenem, ezért először külön-külön beállítom az adatbázisok automatikus backupját a MySQL Administratortban. Ezeket időzítettem is.
Mikor elmentjük az Administratorban a backupokkat, akkor ezek automatikusan megjelennek a Task Scheduler programba.
Keressük meg ezeket. A feladatoknak (task) nézzük meg tulajdonságait (properties). A tulajdonságnál pedig az akciókat (action).
Ezeket én egyenként összegyűjtöttem és létrehoztam egy új feladatot, ahová ezeket felvettem sorrendbe. Ez később lesz előnyös, mert csak egy feladat sikerességét kell vizsgálnunk.
Ezt a gyűjteményt, ami az összes adatbázisról készít egy adatmentést: ezt időzítettem a kívánt időpontra.
Ha mindezzel készen vagyunk, akkor érdemes egy biztonsági lépést is megtennünk.
Létrehozunk egy új task-ot, aminek megadjuk az előidéző eseményét (trigger). Itt hozzáadunk egy új eseményt, amire figyelni fog a rendszer. Én ezt manuálisan állítottam be, a következő XML-t felhasználva:
<QueryList>
<Query Id=”0” Path=”Microsoft-Windows-TaskScheduler/Operational”>
<Select Path=”Microsoft-Windows-TaskScheduler/Operational”>*[System[(EventID=103 or EventID=202 or EventID=203 or EventID=101)]] and *[EventData[Data[1]=’\Az_összes_backup_task’]]</Select>
</Query>
</QueryList>
Ha kicseréljük az ‘Az_összes_backup_task‘-ot a mi feladatunk nevére, akkor ez a task, a ‘figyelmeztető’ akkor fog csak elindulni, amikor az ‘Az_összes_backup_task’ feladat a következő hibakódok egyikével tér vissza: 103,202,203,101. (A kódok jelentését itt találod meg.)
Az akció beállítása rajtunk múlik. Én egy kis python script-et írtam, ami elküld egy e-mail a számomra a tárgyban megjelölve, hogy “Nem futott le a daily backup…”.
(A <select> tag közötti rész egy XPath útvonal. Szabadon módosíthatjuk, ha mást szeretnénk figyelni.)
Utolsó lépés, kapcsoljuk ki a külön-külön futó backupokat a Task Scheduler-be, amiket az Administratorral hoztunk létre.
Hátrány:
Ha meg kell változtatunk a felhasználóhoz tartozó jelszót, akkor az egész folyamatot újra kell csinálni!
