CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Willy
Willy

Reputation: 10646

SQL SERVER: SELECT TOP 1 entire row into "ROW" variable

I am trying to store the entire row as a result of the SELECT TOP * into a kind of "datarow" variable because later, depending on some condition, I need to insert this entire row to a temporary table.

For example, I have done below. The problem is that first I have to assign the values resulting from select top 1 * to variables and then using all these variables to insert into the temporary table, so I want to avoid this, I would like to insert the entire row of select top 1 * into the temporary table at once, see below.

Current scenario:

DECLARE @Field1 varchar(50)
DECLARE @Field2 int
DECLARE @Field3 DateTime

CREATE TABLE #tmpTbl(Field1 varchar(50),  Field2 int, Field3 DateTime)

SELECT TOP 1 @Field1 = field1, @Field2 = field2, @Field3 = field3 from OneTable

// EXECUTE STORE PROCEDURE
DECLARE @ReturnValue int
EXEC @ReturnValue=MySp @Field1

IF @ReturnValue > 0
   INSERT INTO #tmpTbl (Field1, Field2, Field3)
          VALUES (@Field1, @Field2, @Field3)

And I would like to do something like this, if possible:

Desired scenario:

DECLARE @Row SOME_KIND_OF_DATA_ROW_VARIABLE

SET @Row = SELECT TOP 1 field1, field2, field3 from OneTable

// EXECUTE STORE PROCEDURE
DECLARE @ReturnValue int
EXEC @ReturnValue=MySp @Field1

IF @ReturnValue > 0
   INSERT INTO #tmpTbl (Field1, Field2, Field3)
          VALUES @Row <-- Obviously, this is incorrect, but I would like to insert the entire row at once

Upvotes: 1

Views: 3579

Answers (3)

Dan F
Dan F

Reputation: 85

If the time that the row is extracted isn't important (i.e. you can SELECT it out within the IF block happily), you can use an INSERT INTO ... SELECT FROM combination to achieve the desired effect with something like this:

CREATE TABLE #tmpTbl(Field1 varchar(50),  Field2 int, Field3 DateTime)

--EXECUTE STORE PROCEDURE
DECLARE @ReturnValue int
EXEC @ReturnValue=MySp @Field1

IF @ReturnValue > 0
BEGIN
    INSERT INTO #tmpTbl (Field1, Field2, Field3)
    SELECT TOP 1 field1, field2, field3 from OneTable
END

Upvotes: 1

Sergio Prats
Sergio Prats

Reputation: 1213

You can do it this way:

SELECT Aux.Field1, Aux.Field2, Aux.Field3
    INTO #tmpTbl
    FROM  ( SELECT TOP 1 Field1, Field2, Field3 FROM OneTable ) Aux

By doing this you will create #tmpTbl, if it already exists, you will have to DROP before creating it again.

Upvotes: 0

TriV
TriV

Reputation: 5148

You do not need any @Row variable. Just use insert into ... select....

CREATE TABLE #tmpTbl(Field1 varchar(50),  Field2 int, Field3 DateTime)

INSERT INTO #tmpTbl (Field1, Field2, Field3)
SELECT TOP 1 
       field1, field2, field3 
from OneTable

Upvotes: 0

Related Questions