In your query, 2010-4-01 is treated as a mathematical expression, so in essence it read (2010 minus 4 minus 1 is 2005 Converting it to a proper datetime, and using single quotes will fix this issue.) Technically, the parser might allow you to get away with it will do the conversion for you, but in … Read more
Have you verified that there is in fact a row where Staff_Id = @PersonID? What you’ve posted works fine in a test script, assuming the row exists. If you comment out the insert statement, then the error is raised.
I have a SQL Server database and I want to know what columns and types it has. I’d prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?
It’s because the name column on the bar table does not have the UNIQUE constraint. So imagine you have 2 rows on the bar table that contain the name ‘ams’ and you insert a row on baz with ‘ams’ on bar_fk, which row on bar would it be referring since there are two rows matching?
The PARTITION BY clause sets the range of records that will be used for each “GROUP” within the OVER clause. In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you’re de-nomalising the emp table; you still return every record in the emp table.) If there was another column (e.g., state) then you could … Read more
Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation. However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle. Oracle … Read more
String literals Escaping single quotes ‘ by doubling them up -> ” is the standard way and works of course: Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes “, used for identifiers. In old versions or if you still run with standard_conforming_strings = … Read more
There are many tutorial articles about xml parsing with TSQL. For example, http://www.sqlserver.info/syntax/parse-xml-with-sql-server/
The proper way to do this is to use multiple tables and JOIN them in your queries. For example: The person_fruit table contains one row for each fruit a person is associated with and effectively links the person and fruits tables together, I.E. When you want to retrieve a person and all of their fruit … Read more