CODEWITHSUNDEEP
sqlsql-server
mikolaj semeniuk
mikolaj semeniuk

Reputation: 2458

Transfer 1 single random record from one table to another

On mssql server I try transfer single random record from one table to another

my sql script should

  1. Select one random record from table called 'Test1'
  2. Remove this certain record got in step 1. from table 'Test1'
  3. Add this record to table 'Test2'

So far I've found

Get one single random record from table Test1

SELECT TOP 1 
    *
FROM 
    [Test1]
ORDER BY
    NEWID()

Delete records where column called 'uno' is equal '1' and put deleted records in table called 'Test2'

DELETE FROM
   [Test1]
OUTPUT
   deleted.*
FROM
   [Test2]
WHERE
   [uno] = '1'

I tried this which failed

DELETE I FROM (
    SELECT 
        *
    FROM
        [Test1]
    ORDER BY
        NEWID()
) I
OUTPUT
    deleted.*
INTO
    [Test2]

And this which works but transfer whole table instead of only 1 random row

DELETE FROM
    [Test1]
OUTPUT
    deleted.*
INTO
    [Test2]
WHERE EXISTS (
        SELECT TOP 1 
            *
        FROM 
            [Test1]
        ORDER BY
            NEWID()
    )

Also I tried to make adding and deleting separately by catching result of function NEWID() in variable and then insert and delete row with the same unique id in this way

DECLARE @NewReportID uniqueidentifier 
SET @NewReportID = NEWID()

INSERT INTO
   [Test2]
SELECT TOP 1
   *
FROM
   [Test1]
ORDER BY
   @NewReportID
 
GO

DELETE TOP 1 FROM
   [Test1]
ORDER BY
   @NewReportID

but it's failed because there is no way to use ORDER BY with variable

any help would be really appreciated

Upvotes: 2

Views: 44

Answers (1)

Gabriel Durac
Gabriel Durac

Reputation: 2760

WITH CTE AS (
SELECT TOP 1 
    *
FROM 
    [Test1]
ORDER BY
    NEWID()
)
DELETE FROM CTE 
OUTPUT deleted.*
INTO [Test2]

Upvotes: 2

Related Questions