3

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

Improve this question
0

2 Answers 2

Reset to default
14

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.
Improve this answer
4
  • 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) Commented 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. Commented 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. Commented Jan 19, 2018 at 13:01
  • @AndriyM thanks for clearing that up :) So easy to misinterpret something that is written. Commented Jan 19, 2018 at 13:05
5

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,

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.