SQL Server IF NOT EXISTS Usage?
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.
sql
What is the equivalent of ‘describe table’ in SQL Server?
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?
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
I have created tables in MySQL Workbench as shown below : ORDRE table: PRODUKT table: and ORDRELINJE table: so when I try to insert values into ORDRELINJE table i get: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (srdjank.Ordrelinje, CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID)) I’ve … Read more
ERROR: there is no unique constraint matching given keys for referenced table “bar”
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?
Oracle “Partition By” Keyword
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
How do you create a temporary table in an Oracle database?
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
Insert text with single quotes in PostgreSQL
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
Search text in stored procedure in SQL Server
Escape the square brackets: Then the square brackets will be treated as a string literals not as wild cards.
Simple Random Samples from a Sql database
There’s a very interesting discussion of this type of issue here: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ I think with absolutely no assumptions about the table that your O(n lg n) solution is the best. Though actually with a good optimizer or a slightly different technique the query you list may be a bit better, O(m*n) where m is the number … Read more
How to parse XML data in SQL server table
There are many tutorial articles about xml parsing with TSQL. For example, http://www.sqlserver.info/syntax/parse-xml-with-sql-server/