在标量变量中显示我的表的所有数据

i am working on sql and here i have a sql statement where i need to store the data of my sql table in scalar variable

我正在使用sql，这里我有一个sql语句，我需要将我的sql表的数据存储在标量变量中

i did something like this

我做了这样的事

declare @variable1 as varchar(50)
declare @variable2 as varchar(50)
SELECT @variable1 = tid, @variable2 = empname
FROM trainerdetails
select @variable2 as empname,@variable1 as tid

but only one record is being shown in my output

但我的输出中只显示了一条记录

empname     tid
test        354

what i should do here if i want to store all the data of my table in my scalar variable?

如果我想在我的标量变量中存储我的表的所有数据，我该怎么办？

2 个解决方案

#1

scalar variable holds only the last part of data.you will need a table variable

标量变量只保存数据的最后一部分。您将需要一个表变量

declare @tbl table
(
col1 int,
col2 int
)
insert into @tbl
select * from yourtable

#2

A scalar variable, by definition, only holds a single value at a time. So... If you want to fit an entire columns wort of data into one, you'll need to concatenate that data... Usually is the form of a comma separated array. Something along these lines...

根据定义，标量变量一次只能保存一个值。所以...如果你想把整个数据库的数据合二为一，你需要连接这些数据......通常是逗号分隔数组的形式。沿着这些方向......

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;

CREATE TABLE #temp (
    ID INT NOT NULL,
    PersonName VARCHAR(50) NOT NULL 
    );

INSERT #temp (ID, PersonName) VALUES 
    (1, 'Mark'), (2, 'Sally'), (3, 'Joe'), (4, 'Tammy'), (5, 'Bob');

SELECT * FROM #temp t;

--========================================

DECLARE 
    @ID VARCHAR(100) = '', 
    @PersonName VARCHAR(1000) = '';

SELECT 
    @ID = CONCAT(@ID, ', ', t.ID),
    @PersonName = CONCAT(@PersonName, ', ', t.PersonName)
FROM 
    #temp t
ORDER BY 
    t.ID;

SELECT 
    ID = STUFF(@ID, 1, 1, ''),
    PersonName = STUFF(@PersonName, 1, 1, '');

results...

结果...

ID              PersonName
--------------- ------------------------------
 1, 2, 3, 4, 5   Mark, Sally, Joe, Tammy, Bob

HTH, Jason

HTH，杰森

#1