0

My goal is to load data from a webapp into a database on a linked server. Users will create several thousand rows of data with 8 columns.

I was trying to implement a version of the Passing a Table Valued Parameter to a Parameterized SQL Statement in the Table Valued Parameters article from Microsoft.

The big issue I've run into is figuring out how to pass a table valued parameter or temp table into an Execute() where I would put the MERGE.

What's the best way to move data from a web app to a local database then a linked server?

Here's as close as I could get with temp tables:

CREATE TABLE #TempTable
(
    car int not null,
    mileage int not null,
    interface DECIMAL(18,0) not null,
    primary key(car, interface)
);
SELECT 
        123 AS car,
        321 as mileage,
        444 AS interface
INTO #TempTable

EXEC('
CREATE TABLE #TempTable
(
    car int not null,
    mileage int not null,
    interface DECIMAL(18,0) not null,
    primary key(car, interface)
)

MERGE dbo.total AS target
USING #TempTable AS temp
ON temp.car = target.car AND temp.interface = target.interface AND temp.mileage = target.mileage 
WHEN MATCHED THEN
UPDATE SET target.mileage =  1337 
WHEN NOT MATCHED
THEN INSERT (car, interface, mileage) 
VALUES (temp.car, temp.interface, temp.mileage ); 
',#TempTable) at LinkedServer;

DROP TABLE #TempTable

However, this query does nothing. Nothing is being passed.

Here's the closest I've gotten with a temp table parameter:

-- FYI only
CREATE TYPE TempType AS TABLE
(
    car int not null,
    mileage int not null,
    interface DECIMAL(18,0) not null,
    primary key(car, interface)
);

DECLARE @TempTable AS TempType;

INSERT INTO @TempTable (car, mileage, interface)
SELECT 1,1000,123;

SELECT('

DECLARE @TotalTemp AS TempType;

MERGE dbo.TargetTable AS targ 
USING @TotalTemp AS temp
ON temp.car = targ.car AND temp.interface = targ.interface 
WHEN MATCHED THEN
UPDATE SET targ.mileage =  1337 
WHEN NOT MATCHED
THEN INSERT (car, interface, mileage) 
VALUES (temp.car, temp.interface, temp.mileage); 
',@TempTable) at LinkedServer

This query gives a syntax error.

I also realize that accessing a temp table from the linked server to the local server would be a solution. However, I posted this question for the sole purpose of seeing if passing a TVP or temp table to an EXECUTE statement is possible.

Basic.Bear
  • 111
  • 1
  • 9
  • Your remote query creates a new, empty, table `#TempTable` which is then ignored. The `merge` uses `#TempT`. And the executed code doesn't reference any parameters, hence trying to pass `#TempTable` in the `exec` is also ignored. – HABO Apr 11 '20 at 14:56
  • Thanks for your response. I've fixed the syntax errors (I'm trying to hide any business specific identifiers, which can cause some translation errors). However, I've added my TVP version, which just gives me a syntax error. – Basic.Bear Apr 12 '20 at 07:32

0 Answers0