CODEWITHSUNDEEP
sqlsql-server
user8826104
user8826104

Reputation:

SQL - SELECT multiple rows and then INSERT them INTO another table/database

I want to SELECT multiple rows and then INSERT them INTO another table/database.

My current query only works with 1 result, I need it to work with for example, 100:

DECLARE @var INT;

SELECT
    @var = column
FROM
    database.dbo.table1

-- this will produce for example, 100 results

IF (@var IS NULL) -- which it is all 100 times
    INSERT INTO database.dbo.table2
        (column)
    VALUES
        (@var)

How do I do this, can this even be done?

I'm using Microsoft SQL Server Management Studio 2016.

Upvotes: 0

Views: 1358

Answers (2)

Anusha Subashini
Anusha Subashini

Reputation: 397

You can use cursor for insert the data like below


DECLARE @var INT;
Declare AIX Cursor for 
SELECT  column FROM  database.dbo.table1;
Open AIX;
Fetch Next from AIX into  @var;
-- this will produce for example, 100 results
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@var IS  NULL) -- which it is all 100 times
    INSERT INTO database.dbo.table2
        (column)
    VALUES
            (@var)
FETCH NEXT FROM AIX 
    INTO @var;
END
CLOSE AIX;
DEALLOCATE AIX;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I assume you want:

INSERT INTO database.dbo.table2(column)
    SELECT column
    FROM database.dbo.table1
    WHERE column IS NULL;

Upvotes: 2

Related Questions