Search Tables: SELECT c.name AS ‘ColumnName’ ,t.name AS ‘TableName’ FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName; Search Tables and Views: SELECT COLUMN_NAME AS ‘ColumnName’ ,TABLE_NAME AS ‘TableName’ FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName;
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). To … Read more
You need to specify what table you are deleting from. Here is a version with an alias: DELETE w FROM WorkRecord2 w INNER JOIN Employee e ON EmployeeRun=EmployeeNo WHERE Company = ‘1’ AND Date=”2013-05-06″
Try: INSERT INTO table1 ( column1 ) SELECT col1 FROM table2 This is standard ANSI SQL and should work on any DBMS It definitely works for: Oracle MS SQL Server MySQL Postgres SQLite v3 Teradata DB2 Sybase Vertica HSQLDB H2 AWS RedShift SAP HANA Google Spanner
I had same question, and after reading all answers here I definitely stay with SINGULAR, reasons: Reason 1 (Concept). You can think of bag containing apples like “AppleBag”, it doesn’t matter if contains 0, 1 or a million apples, it is always the same bag. Tables are just that, containers, the table name must describe … Read more
The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server. SELECT CAST( CASE WHEN Obsolete=”N” or InStock = ‘Y’ THEN 1 ELSE 0 END AS bit) as Saleable, * FROM Product You only need to use the CAST operator if you want the result as a … Read more
You can use OPENROWSET for this. Have a look. I’ve also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn’t already enabled. CREATE PROC getBusinessLineHistory AS BEGIN SELECT * FROM sys.databases END GO sp_configure ‘Show Advanced Options’, 1 GO RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE GO … Read more
That would have to be: db.users.find({“name”: /.*m.*/}) Or, similar: db.users.find({“name”: /m/}) You’re looking for something that contains “m” somewhere (SQL’s ‘%‘ operator is equivalent to regular expressions’ ‘.*‘), not something that has “m” anchored to the beginning of the string. Note: MongoDB uses regular expressions which are more powerful than “LIKE” in SQL. With regular … Read more
DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement. INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 ) For reference to this have a look at MOC Course 2778A – Writing SQL Queries in SQL Server 2008. For example: INSERT INTO MyTable … Read more