What will be the result of the following T-SQL?

DECLARE @t TABLE (CustomerId int, FirstName varchar(50), LastName varchar(50))

SELECT TOP(100) CustomerId, FirstName, LastName INTO @t
FROM Customer WHERE Country Like 'U%'
 

Experience Level: Senior
Tags: Code challengeDatabasesMS SQL ServerSQL

Answer

Answer

Watch out, there's a trap. SELECT INTO creates new table and inserts data into it. The problem here is that you cannot create table variable @t. It already exists. You can't use SELECT INTO with table variables at all. You can use it to create a table or a temporary table.

The result of the T-SQL will be Incorrect syntax near '@t'.

Now how to fix it?

You could use something like this: 

DECLARE @t TABLE (CustomerId int, FirstName varchar(50), LastName varchar(50))

INSERT INTO @t (CustomerId, FirstName, LastName)
SELECT TOP(100) CustomerId, FirstName, LastName FROM Customer WHERE Country Like 'U%'

The columns are defined explicitly in INSERT INTO on purpose. You could ommit them but it's not a good practice as if someone changes the order of the columns in the table variable, you could get unexpected errors or side effects.

Finally, will the code work with INSERT INTO? It's a good question that is hard to answer without knowing whether the table Customer exists and what is its structure...

Comments

No Comments Yet.
Be the first to tell us what you think.