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

Answer 1

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.

• Andriy M: 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)
• Simon Hellings: @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.
• Andriy M: @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.
• Simon Hellings: @AndriyM thanks for clearing that up :) So easy to misinterpret something that is written.

Answer 2

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,

See also,

• SQL Server Point Constructor or version of ST_Point(x,y)?
• SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?