Create some test data… create table test (a int, b int, c int, constraint pk_test primary key(a, b)); create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c)); create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b)); List indexes … Read more
This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more
DELETE FROM m_productprice B USING m_product C WHERE B.m_product_id = C.m_product_id AND C.upc=”7094″ AND B.m_pricelist_version_id=’1000020′; or DELETE FROM m_productprice WHERE m_pricelist_version_id=’1000020′ AND m_product_id IN (SELECT m_product_id FROM m_product WHERE upc=”7094″);
I am not a fan of stored procedures Stored Procedures are MORE maintainable because: * You don’t have to recompile your C# app whenever you want to change some SQL You’ll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can … Read more
You can do this as follows on SQL Server 2008 or greater: SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, MyTable.UtcColumn), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime FROM MyTable You can also do the less verbose: SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) AS ColumnInLocalTime FROM MyTable Whatever you do, do not use – to subtract dates, because the operation is not atomic, … Read more
SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work. As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best … Read more
You can either have the newly inserted ID being output to the SSMS console like this: INSERT INTO MyTable(Name, Address, PhoneNo) OUTPUT INSERTED.ID VALUES (‘Yatrix’, ‘1234 Address Stuff’, ‘1112223333’) You can use this also from e.g. C#, when you need to get the ID back to your calling app – just execute the SQL query … Read more
The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important. For all other cases, though, do not update identical rows without need. Even if … Read more
SELECT TOP 5 Id, Name FROM customerNames ORDER BY NEWID() That said, everybody seems to come to this page for the more general answer to your question: Selecting a random row in SQL Select a random row with MySQL: SELECT column FROM table ORDER BY RAND() LIMIT 1 Select a random row with PostgreSQL: SELECT … Read more
Try this: Declare @DayOfMonth TinyInt Set @DayOfMonth = 13 Declare @Month TinyInt Set @Month = 6 Declare @Year Integer Set @Year = 2006 — ———————————— Select DateAdd(day, @DayOfMonth – 1, DateAdd(month, @Month – 1, DateAdd(Year, @Year-1900, 0))) It works as well, has added benefit of not doing any string conversions, so it’s pure arithmetic processing … Read more