Sql-server – How to insert into TABLE Variable

datatypessql serversql-server-2016table variable

I want to store 2 coordinate points (latitude, longitude) in a table variable.

I have tried:

declare @coordinates table(latitude1 decimal(12,9), 
                           longitude1 decimal(12,9), 
                           latitude2 decimal(12,9), 
                           longitude2 decimal(12,9)) 

select latitude, 
       longitude into @coordinates 
from   loc.locations 
where  place_name IN ('Delhi', 'Mumbai')
select @coordinates

It's showing error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@coordinates'.

The result of the select query:

select latitude, 
       longitude 
from   loc.locations 
where  place_name IN ('Delhi', 'Mumbai')

is:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

How can I store the values in table datatype?

I ran the query SELECT @@VERSION and got the result:

Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 16299: )

Best Answer

Use this one:

DECLARE @coordinates TABLE (
    latitude1 DECIMAL(12,9),
    longitude1 DECIMAL(12,9)
) 

INSERT into @coordinates
SELECT
    latitude, 
    longitude
FROM loc.locations 
WHERE place_name IN ('Delhi', 'Mumbai');

SELECT * FROM @coordinates

Note:

  1. You created 4 column with NOT NULL behaviors, but your inserting on 2 columns only. It will return an error.

  2. Use INSERT INTO instead of SELECT INTO. Table is already created.

  3. Use SELECT..FROM when calling DECLARE tables.
Related Question