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

Question

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.

just use `INSERT INTO` with a `SELECT`: `INSERT INTO database.dbo.table2 SELECT column FROM database.dbo.table1 WHERE somecondition` — Lamak, Mar 21 '18 at 14:18
Possible duplicate of [How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?](https://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part) — Brien Foss, Mar 21 '18 at 14:34

score 2 · Accepted Answer · answered Mar 21 '18 at 14:18

2

I assume you want:

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

answered Mar 21 '18 at 14:18

Gordon Linoff

1,242,037
58
646
786

sounds about right....which is kinda weird...why would op want to insert just `NULL` values? – Lamak Mar 21 '18 at 14:19
This works, thanks! I'll accept your answer when I can. – Mar 21 '18 at 14:25
@Lamak the OP might need to generate an auto number for each record that exists at table1, without inserting data related to it. It's uncommon but it might happen D: – EzLo Mar 22 '18 at 09:44

score 0 · Answer 2 · answered Mar 21 '18 at 14:49

0

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;

answered Mar 21 '18 at 14:49

Anusha Subashini

387
5
17

1

This is in direct opposition to set-based logic. There is no reason to process inserts from one table to another in a cursor. It is, in fact, an anti-pattern. https://www.red-gate.com/simple-talk/sql/t-sql-programming/rbar-row-by-agonizing-row/ – Eric Brandt Mar 21 '18 at 15:59

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

2 Answers2