How to insert into TABLE Variable?

Question

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

Edgar Allan Bayron · Accepted Answer · 2018-01-19T08:51:15.217

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:

You created 4 column with NOT NULL behaviors, but your inserting on 2 columns only. It will return an error.
Use INSERT INTO instead of SELECT INTO. Table is already created.
Use SELECT..FROM when calling DECLARE tables.

score 5 · Answer 2 · edited Jan 19 '18 at 10:35

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

CREATE TABLE t (
  pt1 geography,
  pt2 geography
);

INSERT INTO t (pt1,pt2) VALUES 
(
  geography::Point(77.216670000, 28.666670000, 4326),
  geography::Point(72.847940000, 19.014410000, 4326)
);

SELECT  pt1.STAsText() AS pt1, pt1.STAsText() AS pt2
FROM t;

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,

How to insert into TABLE Variable?

2 Answers2