SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement?

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'

SQL SERVER - Puzzle - How Does Table Qualifier Work in INSERT Statement? incorrectalias-800x204

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)

, ,
Previous Post
SQL SERVER – Puzzle – Get the First Name From Full Name Without CHARINDEX
Next Post
SQL SERVER – Puzzle – SQL Server Stored Procedure and Unexpected Results

Related Posts

7 Comments. Leave new

  • Adriana Milcov
    March 2, 2018 2:50 pm

    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

    Reply
  • Jason Parker
    March 2, 2018 9:16 pm

    Because you have no FROM statement in the SELECT

    Reply
  • A table named testing exisys.

    Reply
  • Ratheesh K Nair
    March 4, 2018 12:21 pm

    SQL Server ignores the prefix before the column names

    Reply
  • Rick Sellers
    March 6, 2018 2:40 am

    SQL Server ignores the table alias because it already knows the table into which we are inserting data.

    Reply
  • Insertion query never depends on alias:

    This also works:

    INSERT INTO #test(testing.customer_id,testing2.customer_name)
    SELECT 3,’Elon Musk’

    Reply

Leave a Reply

Menu