You have to define a table alias for a derived table in SQL Server:

    SELECT x.* 
      INTO [NEW_TABLE]
      FROM (SELECT * FROM TABLE1
            UNION
            SELECT * FROM TABLE2) x

&quot;x&quot; is the table alias in this example.

You can also try:

    create table new_table as
    select * from table1
    union
    select * from table2

    INSERT INTO #Temp1
    SELECT val1, val2 
    FROM TABLE1
     UNION
    SELECT val1, val2
    FROM TABLE2

Here&#39;s one working syntax for SQL Server 2017:
```
USE [&lt;yourdb-name&gt;]
GO

SELECT * INTO NEWTABLE 
FROM &lt;table1-name&gt;
UNION ALL
SELECT * FROM &lt;table2-name&gt;
```


    select *
    into new_table
    from table_A
    UNION
    Select * 
    From table_B

This only works if Table_A and Table_B have the same schemas

My scenario,
    
how to convert `List&lt;KeyValuePair&lt;string, string&gt;&gt;` into  `IDictionary&lt;string, string&gt;`?

Convert List of KeyValuePair into IDictionary &quot;C#&quot;

I&#39;m trying to use Criteria API in my new project:

    public List&lt;Employee&gt; findEmps(String name) {
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery&lt;Employee&gt; c = cb.createQuery(Employee.class);
		Root&lt;Employee&gt; emp = c.from(Employee.class);
		c.select(emp);
		c.distinct(emp);
		List&lt;Predicate&gt; criteria = new ArrayList&lt;Predicate&gt;();
		
		if (name != null) {
			ParameterExpression&lt;String&gt; p = cb.parameter(String.class, &quot;name&quot;);
			criteria.add(cb.equal(emp.get(&quot;name&quot;), p));
		}

		/* ... */
		
		if (criteria.size() == 0) {
			throw new RuntimeException(&quot;no criteria&quot;);
		} else if (criteria.size() == 1) {
			c.where(criteria.get(0));
		} else {
			c.where(cb.and(criteria.toArray(new Predicate[0])));
		}
		
		TypedQuery&lt;Employee&gt; q = em.createQuery(c);
		
		if (name != null) {
			q.setParameter(&quot;name&quot;, name);
		}
		
		/* ... */
		
		return q.getResultList();
	}

Now when I change this line:

    			criteria.add(cb.equal(emp.get(&quot;name&quot;), p));
to:

    			criteria.add(cb.like(emp.get(&quot;name&quot;), p));

I get an error saying: 

&gt; The method like(Expression&lt;String&gt;, Expression&lt;String&gt;) in the type CriteriaBuilder is not &gt; applicable for the arguments (Path&lt;Object&gt;, ParameterExpression&lt;String&gt;)

What&#39;s the problem?


JPA/Criteria API - Like &amp; equal problem

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn&#39;t work.

Query1: Works

    SELECT * FROM TABLE1
    UNION
    SELECT * FROM TABLE2 

Query2: Does not Work. 
Error: `Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near &#39;)&#39;.`

    SELECT * INTO [NEW_TABLE]
    FROM
    (
    SELECT * FROM TABLE1
    UNION
    SELECT * FROM TABLE2
    )

Thanks!


SELECT INTO USING UNION QUERY

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work.
Query1: Works
<pre><code class="hljs language-sql">SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2 
</code></pre>
Query2: Does not Work.
Error: <code>Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'.</code>
<pre><code class="hljs language-sql">SELECT * INTO [NEW_TABLE]
FROM
(
SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2
)
</code></pre>
Thanks!

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.

The DBMS is Oracle

I did not understand a statement which read like this:

    select ...
    from a,b
    where a.id=b.id(+)

I am confused about the `(+)` operator, and could not get it at any forums... (searching for + within quotes didn&#39;t work either).

Anyway, I used &#39;Explain Plan&#39; of SQLDeveloper and I got an output saying that `HASH JOIN, RIGHT OUTER`, etc.

Would there be any difference if I remove the `(+)` operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before `(+)` can be used?? It would be greatly helpful if you can provide me a simple understanding, or some good links where I can read about this.

Thanks!

Oracle &quot;(+)&quot; Operator

I think the above isolation levels are so alike. Could someone please describe with some nice examples what the main difference is ? 


Difference between &quot;read commited&quot; and &quot;repeatable read&quot;

I have two indexed fields in a table - `type` and `userid` (individual indexes, not a composite). 

`type`s field values are very limited (let&#39;s say it is only 0 or 1), so 50% of table records have the same `type`. `userid` values, on the other hand, come from a much larger set, so the amount of records with the same `userid` is small.

Will any of these queries run faster than the other:

	select * from table where type=1 and userid=5
	select * from table where userid=5 and type=1

Also if both fields were not indexed, would it change the behavior?

Does the order of fields in a WHERE clause affect performance in MySQL?

My database driver for PostgreSQL 8/9 does not return a count of records affected when executing `INSERT` or `UPDATE`. 

PostgreSQL offers the non-standard syntax &quot;[RETURNING][1]&quot; which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count.

&gt; INSERT INTO distributors (did, dname) VALUES (DEFAULT, &#39;XYZ Widgets&#39;)
   **RETURNING did**;


  [1]: http://www.postgresql.org/docs/current/static/sql-insert.html

Get count of records affected by INSERT or UPDATE in PostgreSQL

I want to do something like this:

    DELETE FROM table WHERE id IN (SELECT ....)


How can I do that?

How do I delete multiple rows with different IDs?

While in Management Studio, I am trying to run a query/do a join between two linked servers.
Is this a correct syntax using linked db servers:

    select foo.id 
    from databaseserver1.db1.table1 foo, 
         databaseserver2.db1.table1 bar 
    where foo.name=bar.name

Basically, do you just preface the db server name to the db.table ?

SQL Server Linked Server Example Query

I am trying to select some fields from one table and insert them into an existing table from a stored procedure. Here is what I am trying:

    SELECT col1, col2
    INTO dbo.TableTwo 
    FROM dbo.TableOne 
    WHERE col3 LIKE @search_key

I think `SELECT ... INTO ...` is for temporary tables which is why I get an error that `dbo.TableTwo` already exists.

How can I insert multiple rows from `dbo.TableOne` into `dbo.TableTwo`?




SQL Server SELECT into existing table

Which one should I use to record update made by users?

1. `SYSTEM_USER`, or
2. `ORIGINAL_LOGIN()`, or
3. `SUSER_SNAME()`

SQL Server - current user name

I have a table Administrator with only one column, adminId which is the primary-key. Because of business rules it has to be this way. 

I&#39;d like to understand once and for all how I can write stored procedures that insert values in tables like this. I am using SQL Server and T-SQL and tried using SCOPE_IDENTITY() but that doesn&#39;t work since the table has INSERT_IDENTITY to false or off.

I&#39;d really like to not insert a dummy value just to be able to insert a new row.   Thanks!

Inserting rows into a table with one IDENTITY column only

I&#39;m doing some basic sql on a few tables I have, using a union(rightly or wrongly)

but I need remove the duplicates. Any ideas?

    select * from calls
    left join users a on calls.assigned_to= a.user_id
    where a.dept = 4 
    union
    select * from calls
    left join users r on calls.requestor_id= r.user_id
    where r.dept = 4

remove duplicates from sql union

I want get the value from `Exec(@sql)` and assign to `@Rowcount(int)`

Here is my query:


    &#39;SET @RowCount = (select count(*) 
                        FROM dbo.Comm_Services 
                       WHERE CompanyId = &#39;+cast(@CompanyId as char)+&#39; and &#39;+@condition+&#39;)&#39;



Return value from exec(@sql)

Is it possible to order when the data is come from many select and union it together? Such as

    Select id,name,age
    From Student
    Where age &lt; 15
    Union
    Select id,name,age
    From Student
    Where Name like &quot;%a%&quot;

How can I order this query by name?

I tried this

    Select id,name,age
    From Student
    Where age &lt; 15 or name like &quot;%a%&quot;
    Order by name

But that does not work. 






How to order by with union in SQL?

Are there any methods to do so? I was looking but couldn&#39;t find any.

Another question: I need these methods so I can filter files. 
Some are `AND` filters and some are `OR` filters (like in set theory), so I need to filter according to all files and the unite/intersects ArrayLists that holds those files.

Should I use a different data structure to hold the files? Is there anything else that would offer a better runtime?


Intersection and union of ArrayLists in Java

I have a stored procedure I need to call several different times passing in different paramaters each time. I would like to collect the results as a single dataset. Is something like this possible ...

    exec MyStoredProcedure 1
    UNION
    exec MyStoredProcedure 2
    UNION
    exec MyStoredProcedure 3

  I tried using the syntax above but got the error ...

    Incorrect syntax near the keyword &#39;UNION&#39;

  The stored procedures I am dealing with are pretty complex and sort of a &quot;black box&quot; to me, so I cannot get into the definition of the stored procedure and change anything. Any suggestions on how to gather the results together?

  I am using SQL Server 2008 R2. Thanks for any help.



UNION the results of multiple stored procedures

I&#39;m doing a `UNION` of two queries on an Oracle database. Both of them have a `WHERE` clause. Is there a difference in the performance if I do the `WHERE` after `UNION`ing the queries compared to performing the `UNION` after `WHERE` clause?

For example:

    SELECT colA, colB FROM tableA WHERE colA &gt; 1
    UNION
    SELECT colA, colB FROM tableB WHERE colA &gt; 1

compared to:

    SELECT * 
      FROM (SELECT colA, colB FROM tableA
            UNION
            SELECT colA, colB FROM tableB) 
     WHERE colA &gt; 1

I believe in the second case, it performs a full table scan on both the tables affecting the performance. Is that correct?

UNION with WHERE clause

I want to create different methods for a class called [Multiset][1].

I have all the required methods, but I&#39;m unsure of how to write intersection, union, and subset methods.

For intersection and union, my code starts like this:

    def intersect(var)
      x = Multiset.new
    end

Here is an example:

    X = [1, 1, 2, 4]
    Y = [1, 2, 2, 2]

then the intersection of `X` and `Y` is `[1, 2]`. 

[1]: http://en.wikipedia.org/wiki/Multiset

How can I get the intersection, union, and subset of arrays in Ruby?

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I want to re-use.

It would save time if I did not have to write up a create table command and keep the column list and type list matched up.

Create a temporary table in a SELECT statement without a separate CREATE TABLE

So, its like our normal bank accounts where we have a lot of transactions which result in inflow or outflow of money. The account balance can always be derived by simply summing up the transaction values. What would be better in this case, storing the updated account balance in the database or re-calculating it whenever needed?

Expected transaction volume per account: &lt;5 daily

Expected retrieval of account balance: Whenever a transaction happens and once a day on an average otherwise. 

How would you suggest to take a decision on this? 
Thanks a lot!

Content Type	Original Author	Original Content on Stackoverflow
Question	Sekhar	View Question on Stackoverflow
Solution 1 - Sql	OMG Ponies	View Answer on Stackoverflow
Solution 2 - Sql	Pablo Santa Cruz	View Answer on Stackoverflow
Solution 3 - Sql	Lorena Pita	View Answer on Stackoverflow
Solution 4 - Sql	lucazarts25	View Answer on Stackoverflow
Solution 5 - Sql	Jim	View Answer on Stackoverflow

SELECT INTO USING UNION QUERY

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

JPA/Criteria API - Like & equal problem

Convert List of KeyValuePair into IDictionary "C#"

Attributions