Question of the month January 2016 – Why is table alias ignored for columns specified in INSERT statement?

Look at this code

declare @numbers table(number int)
insert into @numbers (a.number)
select 100
select number from @numbers

It works correctly although the column is specified as a.column and there is no such table or any table with alias a.

The following also looks like a bug with works without any error (There are more multi part object names specified)

declare @numbers table(number int)
insert into @numbers (a.a.a.a.a.a.a.number)
select 100
select number from @numbers

The question is “Why is table alias ignored for columns specified in INSERT statement?”

Advertisement

Always specify length for character data types

Whenever you use character datatypes such as CHAR, VARCHAR, etc, always make sure to specify the length. Otherwise depends on the usage, you get unexpected result or an error

Consider the following example

Declare @name varchar
set @name='SQL Server'
select @name as name

The result is S and not SQL Server because of missing length in the declaration part by default only first character is assigned to the variable

See what happens when you execute the following

select cast('Welcome to SQL Server. Can you read this fully?' as varchar) as name

Do you think the result is W because of missing length?. No. The result is

name
------------------------------
Welcome to SQL Server. Can you

If you count the length it is 30

Consider another example

declare @test table(name varchar)
insert into @test(name)
select 'SQL Server'

This time you get the following error

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.


So you need to be aware of these and always specify the length as you won’t get error for certain cases

Understanding Column alias

There are two methods of creating alias name for a column


SELECT 100 as number
SELECT 100 number


Both result to 100 with column name number

You can use the keyword AS followed by alias name or use a space (But I prefer AS for better clarity and avoid mismatch in the result. See Missing comma and column alias for more information)

Did you know that in SQL Server the following will work same way as above?


SELECT 100number
SELECT 100.number
SELECT 100'number'
SELECT 100"number"
SELECT 100[number]


For string value, except dot followed by alias, all other will work

SELECT 'SQL Server'name
SELECT 'SQL Server' 'name'
SELECT 'SQL Server'"name"
SELECT 'SQL Server'[name]

It should be noted that without space or AS keyword, SQL Server implicitly making succeeding alphabets/alphanumrics as column alias

So you should be aware of this especially the first example