Advantages are that you keep the history (good for auditing) and you don’t have to worry about cascading a delete through various other tables in the database that reference the row you are deleting. Disadvantage is that you have to code any reporting/display methods to take the flag into account. As far as if it … Read more
The raiserror method raiserror(‘Oh no a fatal error’, 20, -1) with log This will terminate the connection, thereby stopping the rest of the script from running. Note that both severity level 20 or higher and the WITH LOG option are necessary for it to work this way. This even works with GO statements, eg. print … Read more
Via SQL as per MSDN SET IDENTITY_INSERT sometableWithIdentity ON INSERT INTO sometableWithIdentity (IdentityColumn, col2, col3, …) VALUES (AnIdentityValue, col2value, col3value, …) SET IDENTITY_INSERT sometableWithIdentity OFF The complete error message tells you exactly what is wrong… Cannot insert explicit value for identity column in table ‘sometableWithIdentity’ when IDENTITY_INSERT is set to OFF.
select MyDate from MyTable order by case when MyDate is null then 1 else 0 end, MyDate
One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. Consider: TableA TableB +————+———-+ +——————–+ |Column1 | Column2 | |Column1 | Column3 | +———————–+ +——————–+ | 1 | 2 | | 1 | 3 | +————+———-+ +———+———-+ The INNER JOIN of TableA and TableB on Column1 will return SELECT * … Read more
Survey Database Design Last Update: 5/3/2015 Diagram and SQL files now available at https://github.com/durrantm/survey If you use this (top) answer or any element, please add feedback on improvements !!! This is a real classic, done by thousands. They always seems ‘fairly simple’ to start with but to be good it’s actually pretty complex. To do … Read more
I assume a single row for each flight? If so: IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id) BEGIN –UPDATE HERE END ELSE BEGIN — INSERT HERE END I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are … Read more
I started with this book: Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jan L. Harrington and found it very clear and helpful and as you get up to speed this one was good too Database Systems: A Practical Approach to Design, Implementation and Management (International Computer Science … Read more
That format requires you to use either: CASE ebv.db_no WHEN 22978 THEN ‘WECS 9500’ WHEN 23218 THEN ‘WECS 9500’ WHEN 23219 THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system Otherwise, use: CASE WHEN ebv.db_no IN (22978, 23218, 23219) THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system
Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples. … Read more