Hot answers tagged

90

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


53

EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES. If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH vals (k,v) AS (VALUES ...


52

SELECT SomeField FROM SomeTable NOLOCK means you've just aliased SomeTable AS NOLOCK. Try the below to see this clearly: SELECT NOLOCK.SomeField FROM SomeTable NOLOCK This obviously has no effect on the locking behaviour of the query. The query doesn't fail because despite being a keyword & showing blue in SSMS, NOLOCK is not a reserved word in ...


50

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...


30

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code. The reasons it's bad: it's not self-documenting -...


26

I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints ...


26

create table as needs a select statement: DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup as select * from ( VALUES (0::int,-99999::numeric), (1::int, 100::numeric) ) as t (key, value); You can also re-write this to use a CTE: create temp table lookup as with t (key, value) as ( values (0::int,-99999::numeric), (1::int,...


20

In most RDBMSs, double-quotes are what are used to specify an exact spelling of something.. (single quotes being string delimiters). SELECT tab."This IS My Column EXACTLY" AS col FROM "My TabLE Name Contains Spaces Too!" tab WHERE tab."ANOTHER UGLY COLUMN name" = 'MyFilterString'; Notice that capital/lowercase also matters when using double-quotes. (...


19

This is documented in UPDATE (Transact-SQL): SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column. In your code example, sum is the (unwise) name of a column, not an aggregate. db<>fiddle demo


12

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. The second plan has a lower estimated cost, so in that limited sense it is 'better'. The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of ...


12

The issue is the datatypes. If you remove them, the statement will work: CREATE TEMP TABLE lookup (key, val) AS VALUES (0, -99999), (1, 100) ; You can define the types by casting the values of the first row: CREATE TEMP TABLE lookup (key, val) AS VALUES (0::bigint, -99999::int), (1, 100) ;


12

Test this code: drop table if EXISTS table_social; create table table_social ( sid varchar(250) primary key, auth char(32) not null, social integer, -- to be checked by triggers female boolean, given varchar(250) not null, family varchar(250) null, photo varchar(1000) null, -- to be ...


12

It's documented with the SELECT statement and it's called "TABLE Command" there. In the SQL standard it's called an "explicit table": The <explicit table>    TABLE <table or query name> is equivalent to the    ( SELECT * FROM <table or query name> ) This seems to be part of the standard at least ...


11

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do: SQL Server Migration Assistant for Oracle (documentation) Microsoft SQL Server Migration Assistant v6.0 for Oracle (download) SQL Server Migration Assistant (SSMA) Team's Blog However in my case it was not as fast as I ...


11

'#' denotes a temporary table. This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key ...


11

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


10

This is to ensure that it is not included in any previous statements since WITH can serve a variety of purposes in T-SQL. If it's the first statement in the batch, I don't think you need it.


10

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result. You can implement FOR XML PATH and STUFF(): SELECT DISTINCT d.AppId, d.AppName, d.AppType, Tags = STUFF((SELECT ', ' + t.TagName FROM AppTags t where d.AppID = t.AppID ...


10

It's SQLCMD What you have is a sqlcmd script. The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt. And sqlcmd has some sqlcmd specfic commands that start with a colon. Some general info on sqlcmd below. You can write SQL statements that are executed after you type GO. In the example, first ...


10

The first argument to the system stored procedure sp_helptext is: [@objname= ] 'name' Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current ...


9

MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers. Some people still teach it Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as ...


9

Yes this is possible. Your query has several syntax errors however. a dash in an identifier is invalid. I used an underscore instead in the below example. If you did create the columns that way, you will need to quote them, e.g. "person-name" the equality operator in SQL is =, not == String constants need to be put in single quotes (') not double quotes ...


9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


8

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN. Your query SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 works the same as SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1 ...


8

Consider below case: +------------+--------------+-----------+ | date | services | downloads | +------------+--------------+-----------+ | 2016-05-31 | Apps | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Apps | 1 | | 2016-05-31 | Videos | 1 | | ...


8

The syntax choice is likely for consistency, albeit redundant. TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword. I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


8

SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins. Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


8

Oracle 12c: Table create table clookup ( clookup_col varchar2( 64 ) ); Add a NOT NULL constraint alter table clookup modify ( clookup_col constraint lookup_9 not null ) ; Optional select table_name , constraint_name , constraint_type from user_constraints where table_name = 'CLOOKUP' ; -- result TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ...


7

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement. Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like: CREATE TABLE `acl_user_role` ( `UserId` INT(10) UNSIGNED NOT NULL, `RoleId` SMALLINT(5) UNSIGNED NOT NULL, PRIMARY KEY (`UserId`,`RoleId`), KEY `...


7

Adding to Craig's comment above - I use Linked servers to refresh a few tables in an Oracle database from SQL Server. You can also pull data to SQL Server using OPENQUERY INSERT INTO dbo.SQLTable1 SELECT * FROM OPENQUERY(ORALINKEDSERV, 'SELECT * FROM OracleTable1') A couple of links that will help you set up linked server here and here


Only top voted, non community-wiki answers of a minimum length are eligible