Pass stored procedure result into a table variable

Question

The stored procedure in question is to be called via a SSIS package. This stopped me from using #TempTable. Apparently those can't be used in a SSIS package situation.

So I thought I'd use a table variable instead, but I can't get it to compile in SQL Server Management Studio.

DECLARE @TempTable TABLE
(
    OrganisationId      INT NOT NULL,
    OrganisationName    NVARCHAR(120) NULL,
    SummaryText         NVARCHAR(MAX) NULL,
    Url                 VARCHAR(8000) NULL,
    Address1            NVARCHAR(255) NULL,
    Address2            NVARCHAR(255) NULL,
    Address3            NVARCHAR(255) NULL,
    City                NVARCHAR(50) NULL,
    County              NVARCHAR(50) NULL,
    Latitude            REAL NULL,
    Longitude           REAL NULL,
    Postcode            VARCHAR(8) NULL,
    LastUpdatedDate     DATETIME NULL,
    Geocode             GEOGRAPHY NULL,
    Contact             VARCHAR(1000) NULL,
    ContactMethodType   INT NULL,
    rn                  INT NULL,
);

INSERT INTO @TempTable
  EXEC [dbo].[GetServiceOrganisations] @ResultsViewListServices;

INSERT INTO @TempTable
  EXEC [dbo].[GetOrganisations] @ResultsViewListOthers;

The error I get in SQL Server Management Studio is a syntax error, here it is:

What is wrong here?

Answer 1

Kudos to Ed Harper for spotting the trailing comma.