Today’s puzzle is very interesting and I am confident that you will find that very interesting. Today’s puzzle is about Table Qualifier. I have not talked about this subject on my blog for a while, and I feel confident that you will like it very much.
Generally, we can use an alias name for a table as columns in SQL Server just fine in our select statement. For example, let us first create a sample table:
CREATE TABLE #test(customer_id INT, customer_name VARCHAR(100)) INSERT INTO #test (customer_id,customer_name) SELECT 1,'Steve Jobs'
We can use an alias name for a table along with the table names. Here is the example
SELECT t1.customer_id, t1.customer_name FROM #test as t1
However, we must make sure that when we use alias we need to make sure that we use a correct alias.
WBut when you qualify table alias for the columns, you should use a correct alias name. If we alias table as t1 for the column we must use the same alias.
But when you qualify wrong table alias for columns, it usually gives an error. Let us try with following script.
SELECT t1.customer_id, t1.customer_name FROM #test as t2
you get the following error
Msg 4104, Level 16, State 1, Line 73
The multi-part identifier “t1.customer_id” could not be bound.
Msg 4104, Level 16, State 1, Line 73
The multi-part identifier “t1.customer_name” could not be bound.
Now let us see our today’s puzzle!
SQL Puzzle
How does the following work fine although there is no table alias named “testing”?
INSERT INTO #test (testing.customer_id,customer_name) SELECT 2,'Bill Gates'
Please leave your answers in the comments section. I will take publish the answers next week at the same time.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
SQL server ignores completely the qualifications for the columns in insert statement because it consider implicitly the table specified in the INTO caluse, but of course accept the qualifications for the table.
/*Demo -How SQL Server uses table Qualifier in Insert Statement*/
–create 2 identical TBs in 2 separtate DBs
create database DB1
go
use DB1
create table tb_test (customer_id INT, customer_name VARCHAR(100) )
create database DB2
go
use DB2
create table tb_test (customer_id INT, customer_name VARCHAR(100) )
–insert data from DB1 into DB2 using qualifier for the columns
use DB1
insert into tb_test(DB1.dbo.customer_id, customer_name) values (1, ‘Steve Jobs’)
insert into tb_test(DB2.dbo.customer_id, customer_name) values (2, ‘Bill Gates’)
— but the columns qualifications are ignored and inserts are made into DB1
select * from DB1.dbo.tb_test
select * from DB2.dbo.tb_test
DB1:
1 Steve Jobs
2 Bill Gates
DB2:
–
–and of course table qualifications are not ignore
insert into DB1.dbo.tb_test(customer_id, customer_name) values (3, ‘Elon Musk’)
insert into DB2.dbo.tb_test(customer_id, customer_name) values (1, ‘Edgar. F. Codd’)
select * from DB1.dbo.tb_test
select * from DB2.dbo.tb_test
DB1:
1 Steve Jobs
2 Bill Gates
3 Elon Musk
DB2:
1 Edgar. F. Codd
Because you have no FROM statement in the SELECT
A table named testing exisys.
No. You can try the same code with TTT (non existing table). It works too
SQL Server ignores the prefix before the column names
SQL Server ignores the table alias because it already knows the table into which we are inserting data.
Insertion query never depends on alias:
This also works:
INSERT INTO #test(testing.customer_id,testing2.customer_name)
SELECT 3,’Elon Musk’