Yes. You need to prefix the table name with &quot;#&quot; (hash) to create temporary tables.

If you do NOT need the table later, go ahead &amp; create it.
     Temporary Tables are very much like normal tables. However, it gets created in tempdb.
Also, it is only accessible via the current session i.e. For EG: if another user tries to access the temp table created by you, he&#39;ll not be able to do so.

&quot;##&quot; (double-hash creates &quot;Global&quot; temp table that can be accessed by other sessions as well.

Refer the below link for the Basics of Temporary Tables:
http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

If the content of your table is less than 5000 rows &amp; does NOT contain data types such as nvarchar(MAX), varbinary(MAX), consider using Table Variables.

&lt;s&gt;They are the fastest as they are just like any other variables which are stored in the RAM.&lt;/s&gt; [They are stored in tempdb as well, not in RAM](https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386).


 
    DECLARE @ItemBack1 TABLE
    (
     column1 int,
     column2 int,
     someInt int,
     someVarChar nvarchar(50)
    );

    INSERT INTO @ItemBack1
    SELECT column1, 
           column2, 
           someInt, 
           someVarChar 
      FROM table2
     WHERE table2.ID = 7;


More Info on Table Variables:
http://odetocode.com/articles/365.aspx

The difference between this two tables `ItemBack1` and `#ItemBack1` is that the first on is persistent (permanent) where as the other is temporary.

Now if take a look at your question again 
&gt;Is it necessary to Use # for creating temp table in sql server?

The answer is **Yes**, because without this preceding `#` the table will not be a temporary table, it will be independent of all sessions and scopes.

Travis CI continuous integration service officially supports many [languages](http://about.travis-ci.org/docs/#Specific-Language-Help), but not C# or F#. 

Can I use it with my .net projects?

How do I use Travis-CI with C# or F#

I have installed `postgresql-9.2.4` from the source, now in rails app when I execute:

 `rake db:create` command I get:

    $ bin/rake db:create RAILS_ENV=&quot;test&quot;
    PG::Error: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
    HINT:  Use the same encoding as in the template database, or use template0 as template.
    : CREATE DATABASE &quot;verticals_test&quot; ENCODING = &#39;unicode&#39;
    /home/vagrant/my-project/.gems/ruby/1.9.1/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:652:in `exec
    ....
    bin/rake:16:in `load&#39;
    bin/rake:16:in `&lt;main&gt;&#39;
    Couldn&#39;t create database for {&quot;adapter&quot;=&gt;&quot;postgresql&quot;, &quot;encoding&quot;=&gt;&quot;unicode&quot;, &quot;database&quot;=&gt;&quot;my_db&quot;, &quot;host&quot;=&gt;&quot;localhost&quot;, &quot;pool&quot;=&gt;5, &quot;username&quot;=&gt;&quot;my_user&quot;, &quot;password&quot;=&gt;&quot;my_password&quot;}

any idea?

PG::Error: ERROR: new encoding (UTF8) is incompatible

Is it necessary to use `#` before creating a temporary table in SQL server?

Example:

    
    SELECT column1, column2, someInt, someVarChar 
    INTO ItemBack1 
    FROM table2
    WHERE table2.ID = 7


For ItemBack1 is it necessary to use the `#` symbol? 

If not, then what is the use of `#` in creating temp tables?

Is it necessary to use # for creating temp tables in SQL server?

Is it necessary to use <code>#</code> before creating a temporary table in SQL server?
Example:
<pre><code class="hljs language-sql">SELECT column1, column2, someInt, someVarChar 
INTO ItemBack1 
FROM table2
WHERE table2.ID = 7
</code></pre>
For ItemBack1 is it necessary to use the <code>#</code> symbol?
If not, then what is the use of <code>#</code> in creating temp tables?

SQL How to replace values of select return?

My inputs are in this way.

    col1   col2
    1      a
    1      b
    2      c
    2      d
    2      e


O/p: Should Be like

    col1    col2
    1       a,b
    2       c,d,e

I want a query that can be fired at DB level. I&#39;ve tried various ways, but wasn&#39;t able to make this out...

Is there any function in oracle similar to group_concat in mysql?

I&#39;m running this SQL query:

    SELECT wp_woocommerce_order_items.order_id As No_Commande
    FROM  wp_woocommerce_order_items
    LEFT JOIN 
    	(
    		SELECT meta_value As Prenom
    		FROM wp_postmeta
    		WHERE meta_key = &#39;_shipping_first_name&#39;
    	) AS a
    ON wp_woocommerce_order_items.order_id = a.post_id
    WHERE  wp_woocommerce_order_items.order_id =2198

And I get this error:

&gt;  #1054 - Unknown column &#39;a.post_id&#39; in &#39;on clause&#39;.

I think my code is pretty simple, but I can&#39;t make it right. What am I doing wrong? 

SQL LEFT JOIN Subquery Alias

I am a MSSQL user and now I am converting my database to MySQL. I am writing the following query in MySQL:

    SELECT * INTO new_tbl FROM tbl;

And I get the following error

    Error : Undeclared variable new_tbl

How such a query should be properly written in MySQL?

SELECT INTO in MySQL

I have a large excel worksheet that I want to add to my database.

Can I generate an SQL insert script from this excel worksheet?

Generate sql insert script from excel worksheet

I have a string that is up to 3 characters long when it&#39;s first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was &#39;1&#39; then the new value would be &#39;001&#39;. Or if its original value was &#39;23&#39; the new value is &#39;023&#39;. Or if its original value is &#39;124&#39; then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

Pad a string with leading zeros so it&#39;s 3 characters long in SQL Server 2008

I have developed a query, and in the results for the first three columns I get `NULL`.  How can I replace it with `0`? 

      Select c.rundate, 
        sum(case when c.runstatus = &#39;Succeeded&#39; then 1 end) as Succeeded, 
        sum(case when c.runstatus = &#39;Failed&#39; then 1 end) as Failed, 
        sum(case when c.runstatus = &#39;Cancelled&#39; then 1 end) as Cancelled, 
        count(*) as Totalrun from
        (    Select a.name,case when b.run_status=0 Then &#39;Failed&#39; when b.run_status=1 Then &#39;Succeeded&#39;
        when b.run_status=2 Then &#39;Retry&#39; Else &#39;Cancelled&#39; End as Runstatus,
        ---cast(run_date as datetime)
                    cast(substring(convert(varchar(8),run_date),1,4)+&#39;/&#39;+substring(convert(varchar(8),run_date),5,2)+&#39;/&#39;          +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate
        from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock) 
        on a.job_id=b.job_id
        where a.name=&#39;AI&#39;
        and b.step_id=0) as c
        group by 
        c.rundate

Replacing NULL with 0 in a SQL server query

I&#39;m trying to perform an update and a select ... basically, update based on an index, and then select the row id that was updated.

This is simple using the OUTPUT clause:

    UPDATE Foo
    SET Bar = 1
    OUTPUT INSERTED.Id
    WHERE Baz = 2

But now, how do I get this into a variable?

    DECLARE @id INT

These three don&#39;t work:

    UPDATE Foo
    SET Bar = 1
    OUTPUT @id = INSERTED.Id
    WHERE Baz = 2

    SET @id =
    (UPDATE Foo
     SET Bar = 1
     OUTPUT INSERTED.Id
     WHERE Baz = 2)

    SET @id =
    (SELECT Id FROM (UPDATE Foo
                     SET Bar = 1
                     OUTPUT INSERTED.Id Id
                     WHERE Baz = 2) z)

That last one included because it had me temporarily excited when all the red squigglies went away in Management Studio. Alas, I get this error:

    A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

UPDATE OUTPUT into a variable

I have a problem to decide whether to use a view or a temp table.

I have a stored procedure that i call from program. In that SP i store the result of a long query in a temp table, name the columns and make another queries on that table store the results in labels or a gridview or whatever and drop the Temp Table. I could also store the query-result in a view and make queries on that view. So what is better or in what case do i HAVE to use a VIEW/ Temp Table.

According to my research a view has the benefit of: Security, Simplicity and Column Name Specification. My temporary table fulfills all that too (according to my opinion).  

View or Temporary Table - which to use in MS SQL Server?

I&#39;m wondering if I can select the value of a column if the column exists and just select null otherwise.  In other words I&#39;d like to &quot;lift&quot; the select statement to handle the case when the column doesn&#39;t exist.

    SELECT uniqueId
        ,  columnTwo
        ,  /*WHEN columnThree exists THEN columnThree ELSE NULL END*/ AS columnThree
    FROM (subQuery) s

Note, I&#39;m in the middle to solidifying my data model and design.  I hope to exclude this logic in the coming weeks, but I&#39;d really like to move beyond this problem right because the data model fix is a more time consuming endeavor than I&#39;d like to tackle now.

Also note, I&#39;d like to be able to do this in one query.  So I&#39;m not looking for an answer like 

&gt; check what columns are on your sub query first.  Then modify your
&gt; query to appropriately handle the columns on your sub query.

Select columnValue if the column exists otherwise null

I have a query in `SQL`, I have to get a date in a format of `dd/mm/yy` 

Example: `25/jun/2013`. 

How can I `convert` it for `SQL server`?

Convert Date format into DD/MMM/YYYY format in SQL Server

I&#39;ve got a table with two columns, `ID` and `Value`. I want to change a part of some strings in the second column.

Example of Table:

&lt;!-- language: lang-none --&gt;

    ID            Value
    ---------------------------------
    1             c:\temp\123\abc\111
    2             c:\temp\123\abc\222
    3             c:\temp\123\abc\333
    4             c:\temp\123\abc\444

Now the `123\` in the `Value` string is not needed. I tried `UPDATE` and `REPLACE`:

    UPDATE dbo.xxx
    SET Value = REPLACE(Value, &#39;%123%&#39;, &#39;&#39;)
    WHERE ID &lt;= 4

When I execute the script SQL Server does not report an error, but it does not update anything either. Why is that?


UPDATE and REPLACE part of a string

I&#39;ve written a very simple JDBC login test program. And after all kinds of problems I&#39;ve almost got it working. Almost, just can&#39;t seem to get past this problem:

&gt; SQLServerException: Login failed for user xxxxx

I created a simple database PersonInfo then I created user user1 password1 (SQL authentication). And after trying everything was unable to connect to the database.

I am using SqlServer2008 on Win 7, I&#39;ve got the latest JDBC driver from Microsoft. 

My code is:

    import java.sql.*;
    
    public class hell {
    public static void main(String[] args) {
    	
    	try {
    		Class.forName(&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;).newInstance();
    Connection conn=  DriverManager.getConnection(&quot;jdbc:sqlserver://localhost:1433;databaseName=PersonInfo;user=Sohaib;password=0000;&quot;);
    
    
    System.out.println(&quot;connected&quot;);
           }
    
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    }


Here&#39;s the Exception  

    Exception: Unable to get connect
    com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user &#39;Sohaib&#39;.
    and all other supporting errors.

SqlServer: Login failed for user

I&#39;m trying to **add custom time to datetime in SQL Server 2008 R2**.

Following is what I&#39;ve tried. 

    SELECT DATEADD(hh, 03, DATEADD(mi, 30, DATEADD(ss, 00, DATEDIFF(dd, 0,GETDATE())))) as Customtime

Using the above query, I&#39;m able to achieve it.  

But is there any shorthand method already available to add custom time to datetime?

How to add time to DateTime in SQL

I am trying to drop a table but getting the following message:

&gt; Msg 3726, Level 16, State 1, Line 3  
&gt; Could not drop object &#39;dbo.UserProfile&#39; because it is referenced by a FOREIGN KEY constraint.  
&gt; Msg 2714, Level 16, State 6, Line 2  
&gt; There is already an object named &#39;UserProfile&#39; in the database.

I looked around with SQL Server Management Studio but I am unable to find the constraint. How can I find out the foreign key constraints?

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

I have a table that contains a `Xml` column:

    SELECT * 
    FROM Sqm

![enter image description here][1]

A sample of the `xml` data of a row would be:


    &lt;Sqm version=&quot;1.2&quot;&gt;
      &lt;Metrics&gt;
        &lt;Metric id=&quot;TransactionCleanupThread.RecordUsedTransactionShift&quot; type=&quot;timer&quot; unit=&quot;&#181;s&quot; count=&quot;1&quot; sum=&quot;21490&quot;   average=&quot;21490&quot;   minValue=&quot;73701&quot;    maxValue=&quot;73701&quot;                               &gt;73701&lt;/Metric&gt;
        &lt;Metric id=&quot;TransactionCleanupThread.RefundOldTrans&quot;             type=&quot;timer&quot; unit=&quot;&#181;s&quot; count=&quot;1&quot; sum=&quot;184487&quot;  average=&quot;184487&quot;  minValue=&quot;632704&quot;   maxValue=&quot;632704&quot;                              &gt;632704&lt;/Metric&gt;
        &lt;Metric id=&quot;Database.CreateConnection_SaveContextUserGUID&quot;       type=&quot;timer&quot; unit=&quot;&#181;s&quot; count=&quot;2&quot; sum=&quot;7562&quot;    average=&quot;3781&quot;    minValue=&quot;12928&quot;    maxValue=&quot;13006&quot;    standardDeviation=&quot;16&quot;     &gt;12967&lt;/Metric&gt;
        &lt;Metric id=&quot;Global.CurrentUser&quot;                                  type=&quot;timer&quot; unit=&quot;&#181;s&quot; count=&quot;6&quot; sum=&quot;4022464&quot; average=&quot;670411&quot;  minValue=&quot;15&quot;       maxValue=&quot;13794345&quot; standardDeviation=&quot;1642047&quot;&gt;2299194&lt;/Metric&gt;
        &lt;Metric id=&quot;Global.CurrentUser_FetchIdentityFromDatabase&quot;        type=&quot;timer&quot; unit=&quot;&#181;s&quot; count=&quot;1&quot; sum=&quot;4010057&quot; average=&quot;4010057&quot; minValue=&quot;13752614&quot; maxValue=&quot;13752614&quot;                            &gt;13752614&lt;/Metric&gt;
      &lt;/Metrics&gt;
    &lt;/Sqm&gt;

In the case of this data, I would want:

    SqmId  id                                                   type   unit  count  sum      minValue  maxValue  standardDeviation  Value
    =====  ===================================================  =====  ====  =====  ======   ========  ========  =================  ======
    1      TransactionCleanupThread.RecordUsedTransactionShift  timer  &#181;s    1      21490    73701     73701     NULL               73701
    1      TransactionCleanupThread.RefundOldTrans              timer  &#181;s    1      184487   632704    632704    NULL               632704
    1      Database.CreateConnection_SaveContextUserGUID        timer  &#181;s    2      7562     12928     13006     16                 12967
    1      Global.CurrentUser                                   timer  &#181;s    6      4022464  15        13794345  1642047            2299194
    1      Global.CurrentUser_FetchIdentityFromDatabase         timer  &#181;s    1      4010057  13752614  13752614  NULL               13752614
    2      ...

In the end I&#39;ll actually be performing `SUM()`, `MIN()`, `MAX()` aggregation. But for now I&#39;m just trying to *query* an xml column.

In pseudo-code, I would try something like:

    SELECT
        SqmId,
    	Data.query(&#39;/Sqm/Metrics/Metric/@id&#39;) AS id,
    	Data.query(&#39;/Sqm/Metrics/Metric/@type&#39;) AS type,
    	Data.query(&#39;/Sqm/Metrics/Metric/@unit&#39;) AS unit,
    	Data.query(&#39;/Sqm/Metrics/Metric/@sum&#39;) AS sum,
    	Data.query(&#39;/Sqm/Metrics/Metric/@count&#39;) AS count,
    	Data.query(&#39;/Sqm/Metrics/Metric/@minValue&#39;) AS minValue,
    	Data.query(&#39;/Sqm/Metrics/Metric/@maxValue&#39;) AS maxValue,
    	Data.query(&#39;/Sqm/Metrics/Metric/@standardDeviation&#39;) AS standardDeviation,
    	Data.query(&#39;/Sqm/Metrics/Metric&#39;) AS value
    FROM Sqm

But that SQL query doesn&#39;t work:

&gt; Msg 2396, Level 16, State 1, Line 2  
&gt; XQuery [Sqm.data.query()]: Attribute may not appear outside of an element


I&#39;ve hunted, and it&#39;s amazing how poorly documented, or exampled, Xml querying is. Most resources rather than querying a **table**, query a **variable**; which I&#39;m not doing. Most resources only use xml querying for filtering and selection, rather than reading values. Most resources read hard-coded child nodes (by index), rather than actual values.

Related resources that I read
--------------------
- https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
- https://stackoverflow.com/questions/12913724/sql-server-query-xml-attribute-for-an-element-value
- https://stackoverflow.com/questions/14343263/sql-querying-xml-attributes
- [SQL Server 2005 XQuery and XML-DML - Part 1][2]
- [BOL: XML Support in Microsoft SQL Server 2005][3]
- [Querying XML in SQL Server][4]
- [Basic SQL Server XML Querying][5]
- [BOL: query() Method (xml Data Type)][6]
- [XML Workshop V - Reading Values from XML Columns][7]
- [SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer][8]

Update: .value rather than .query
============================

I tried randomly using `.value`, in place of `.query`:

    SELECT
    	Sqm.SqmId,
    	Data.value(&#39;/Sqm/Metrics/Metric/@id&#39;, &#39;varchar(max)&#39;) AS id,
    	Data.value(&#39;/Sqm/Metrics/Metric/@type&#39;, &#39;varchar(max)&#39;) AS type,
    	Data.value(&#39;/Sqm/Metrics/Metric/@unit&#39;, &#39;varchar(max)&#39;) AS unit,
    	Data.value(&#39;/Sqm/Metrics/Metric/@sum&#39;, &#39;varchar(max)&#39;) AS sum,
    	Data.value(&#39;/Sqm/Metrics/Metric/@count&#39;, &#39;varchar(max)&#39;) AS count,
    	Data.value(&#39;/Sqm/Metrics/Metric/@minValue&#39;, &#39;varchar(max)&#39;) AS minValue,
    	Data.value(&#39;/Sqm/Metrics/Metric/@maxValue&#39;, &#39;varchar(max)&#39;) AS maxValue,
    	Data.value(&#39;/Sqm/Metrics/Metric/@standardDeviation&#39;, &#39;varchar(max)&#39;) AS standardDeviation,
    	Data.value(&#39;/Sqm/Metrics/Metric&#39;, &#39;varchar(max)&#39;) AS value
    FROM Sqm

But that also doesn&#39;t work: 

&gt; Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:  
&gt; &#39;value()&#39; requires a singleton (or empty sequence), found operand of
&gt; type &#39;xdt:untypedAtomic *&#39;


  [1]: http://i.stack.imgur.com/nVgUG.png
  [2]: http://www.codeguru.com/csharp/.net/net_data/article.php/c19491/SQL-Server-2005-XQuery-and-XMLDML--Part-1.htm
  [3]: http://msdn.microsoft.com/en-us/library/ms345117.aspx
  [4]: http://blog.jontav.com/post/6811942997/querying-xml-in-sql-server
  [5]: http://www.mssqltips.com/sqlservertip/2889/basic-sql-server-xml-querying/
  [6]: http://technet.microsoft.com/en-us/library/ms191474.aspx
  [7]: http://jacobsebastian.blogspot.ca/2007/11/xml-workshop-v-reading-values-from-xml.html
  [8]: http://blog.sqlauthority.com/2012/04/27/sql-server-introduction-to-discovering-xml-data-type-methods-a-primer/

How to query for Xml values and attributes from table in SQL Server?

My table looks something like this:

    group    date      cash  checks
      1    1/1/2013     0      0
      2    1/1/2013     0      800
      1    1/3/2013     0      700
      3    1/1/2013     0      600
      1    1/2/2013     0      400
      3    1/5/2013     0      200

-- Do not need cash just demonstrating that table has more information in it

I want to get the each unique group where date is max and checks is greater than 0. So the return would look something like:

    group    date     checks
      2    1/1/2013    800
      1    1/3/2013    700
      3    1/5/2013    200

attempted code:

    SELECT group,MAX(date),checks
    	FROM table
    	WHERE checks&gt;0
    	GROUP BY group
    	ORDER BY group DESC

problem with that though is it gives me all the dates and checks rather than just the max date row.


using ms sql server 2005

Select info from table where row has max date

I have a simple select statement. I want to add a temporary column which will number the rows in my result set. I tried this - 

    declare @num int
    set @num = 0;
    select t.A, t.B, t.C, (@count + 1) as number
    from tableZ as t

It assigns the 1 to all rows. I tried @count = @count + 1 and it did not work.
How do I do this thing in a simple manner ?

thanks.



Add a row number to result set of a SQL query

I googled a lot, but I did not find the exact straight forward answer with an example. 

Any example for this would be more helpful.

Content Type	Original Author	Original Content on Stackoverflow
Question	Arun CM	View Question on Stackoverflow
Solution 1 - Sql	mobiledaemon	View Answer on Stackoverflow
Solution 2 - Sql	zzlalani	View Answer on Stackoverflow

Is it necessary to use # for creating temp tables in SQL server?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

PG::Error: ERROR: new encoding (UTF8) is incompatible

How do I use Travis-CI with C# or F#

Attributions