TSQL - SOME | ANY why are they same with different names?
From the ANSI-92 SQL Standard (search for "SOME"). Also here, text
<some> ::= SOME | ANY
I suspect the reason is that SQL language comes from the early 1970s, but had no standard until 1986. The standard would have taken elements of the existing SQL dialects, so we have this SOME/ANY anomaly.
This blog article by Brad Schulz explains some differences: "ALL, ANY, and SOME: The Three Stooges"
How to deal with SQL column names that look like SQL keywords?
Wrap the column name in brackets like so, from
becomes [from].
select [from] from table;
It is also possible to use the following (useful when querying multiple tables):
select table.[from] from table;
Can there be constraints with the same name in a DB?
No - a constraint is a database object as well, and thus its name needs to be unique.
Try adding e.g. the table name to your constraint, that way it'll be unique.
CREATE TABLE BankAccount
(
BankAccountID INT PRIMARY KEY,
EmployerCode VARCHAR(20) NOT NULL,
Amount MONEY NOT NULL,
CONSTRAINT FK_BankAccount_Employer
FOREIGN KEY (EmployerCode) REFERENCES Employer
)
We basically use "FK_"(child table)_(parent table)" to name the constraints and are quite happy with this naming convention.
Information from MSDN
That constraint names have to be unique to the schema (ie. two different schemas in the same database can both contain a constraint with the same name) is not explicitly documented. Rather you need to assume the identifiers of database objects must be unique within the containing schema unless specified otherwise. So the constraint name is defined as:
Is the name of the constraint. Constraint names must follow the rules for identifiers, except that the name cannot start with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.
Compare this to the name of an index:
Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.
which explicitly narrows the scope of the identifier.
tsql sql multiple temp tables with same name
Broadly speaking....
Local temp tables (using a single #
) are only available to the instance of the procedure that called them or procedures that were called from the same procedure as the one that created the temp table.
If you call a procedure 10 times simultaneously, each has it's own "copy" of the temp table.
From your description it sounds like you just have 4 datasets, each running a Stored Procedure and each of these SP's creates a temp table called #temp
. You don't have an SP that calls the other SP's...
Assuming that's correct then it will work fine, in fact you don't even have to drop the temp table at the end of the proc as this done automatically.
The fact that the temp tables are all referenced using the same name doesn't matter, the actual name of the physical temp table stored in the database will not be #temp but something more like tempdb.dbo.#temp_______________000000000123
, the point is that they are in fact all unique tables in the database but SQL Server handles the translation to/from the name you created it under automatically.
Select rows with same id but different value in another column
This ought to do it:
SELECT *
FROM YourTable
WHERE ARIDNR IN (
SELECT ARIDNR
FROM YourTable
GROUP BY ARIDNR
HAVING COUNT(*) > 1
)
The idea is to use the inner query to identify the records which have a ARIDNR
value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.
Query error with ambiguous column name in SQL
We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.
Following is a an example solution implementation of concept explained above
I think you have ambiguity only in InvoiceID
that exists both in InvoiceLineItems
and Invoices
Other fields seem distinct. So try This
I just replace InvoiceID with Invoices.InvoiceID
SELECT
VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM Vendors
JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
WHERE
Invoices.InvoiceID IN
(SELECT InvoiceSequence
FROM InvoiceLineItems
WHERE InvoiceSequence > 1)
ORDER BY
VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers
sql query to return differences between two tables
IF you have tables A
and B
, both with colum C
, here are the records, which are present in table A
but not in B
:
SELECT A.*
FROM A
LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL
To get all the differences with a single query, a full join must be used, like this:
SELECT A.*, B.*
FROM A
FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL
What you need to know in this case is, that when a record can be found in A
, but not in B
, than the columns which come from B
will be NULL, and similarly for those, which are present in B
and not in A
, the columns from A
will be null.
How do I compare two columns for equality in SQL Server?
What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.
CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult
should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.
Sql Server stored procedure cursor name conflict
Apparently there is a local keyword.
http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c7177
Why do table names in SQL Server start with dbo?
dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.
Related Topics
Order by Column1 If Column1 Is Not Null, Otherwise Order by Column2
Generated Excel from Ssis But Getting Quote in Every Column
Sql Dynamic Order by Using Alias
Postgresql: Table Name/Schema Confusion
Display All Data of All Tables
MySQL to Get The Count of Rows That Fall on a Date for Each Day of a Month
How to Insert into a Table with Just One Identity Column (Sql Express)
Are SQL Queries Guaranteed to Execute Atomically When Using Union
Using Where Clause with Between and Null Date Parameters
A Simple SQL Select Query to Crawl All Connected People in a Social Graph
Change Data Type Varchar to Varbinary(Max) in SQL Server
How Do The SQL "Is" and "=" Operators Differ
How to Use Oracle Outer Join with a Filter Where Clause
Why Is There a Scan on My Clustered Index
Sql Server Bigint or Decimal(18,0) for Primary Key
Call Dll Function from SQL Stored Procedure Using The Current Connection
How to Add "Weights" to a MySQL Table and Select Random Values According to These