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, 
from   loc.locations 
where  place_name IN ('Delhi', 'Mumbai')


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: )


2 Answers 2


Use this one:

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

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

SELECT * FROM @coordinates


  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.
  • 1
    You created 4 column with NOT NULL behaviors – actually they didn't. None of the columns is declared with an explicit NULL or NOT NULL, which makes the former the default, so all columns are nullable and there will be no error. I'm not sure why two sets of columns but maybe that's part of the problem (how to populate two sets of columns from two rows)
    – Andriy M
    Jan 19, 2018 at 8:58
  • @AndriyM, are you sure? If using insert and not specifying the columns, trying to {insert @x select y} will result in a "Column name or number of supplied values does not match table definition" error. Jan 19, 2018 at 11:58
  • 1
    @SimonHellings: I was commenting specifically on the NOT NULL point raised by Edgar. (Although I have since been slightly corrected on that as well, because apparently the default nullability of columns can be controlled through SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON). Regarding the syntax, yes, you are perfectly correct, when you are not specifying target columns in INSERT, you must supply values for all writeable columns of the target table.
    – Andriy M
    Jan 19, 2018 at 13:01
  • @AndriyM thanks for clearing that up :) So easy to misinterpret something that is written. Jan 19, 2018 at 13:05

These are Spatial Coordinates, so you should store them with a Spatial Geography Point

  pt1 geography,
  pt2 geography

  geography::Point(77.216670000, 28.666670000, 4326),
  geography::Point(72.847940000, 19.014410000, 4326)

SELECT  pt1.STAsText() AS pt1, pt1.STAsText() AS pt2

pt1                        pt2
POINT (28.66667 77.21667)  POINT (28.66667 77.21667)

The third parameter (4326) is the Spatial Reference Identifier (SRIDs). "The SRID corresponds to a spatial reference system based on the specific ellipsoid used for ... mapping." SQL Server currently only supports this one value.

Note, if they're direction ie, pt1-pt2 represents a plane-route or something, I would use a Line instead,

See also,


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.