CODEWITHSUNDEEP
sqlsql-serverwhile-loop
Álvaro García
Álvaro García

Reputation: 19396

how can I get a variable with all the results and iterate over it?

I have a table with some records, about 40, called Table1.

I would like to create a new register in another table for each these records, but I don't know how can I do it. I would like to iterate with a while loop for each record and insert the record in the other table, but I don't how (the syntax).

I would like something like this:

foreach record in myTable1
     Insert into Table2(IDTable1) VALUES(record.IDTable1)
end foreach

I don't know scripts in SQL Server.

Thanks.

Upvotes: 0

Views: 60

Answers (2)

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

SQL Server, for the most part, is set-based. That means that queries and rows are down in sets, group and filtered by clauses. Only in rare cases will you want or need to iterate over every single row in a result set and do something with it.

I'd say in your case, you're wanting to copy the contents of one table to another. You can do that several ways, but one of the easiest is something like this:

SELECT
  IDTable1
INTO
  Table2
FROM
  Table1

This assumes that Table2 does not exist, and will create it. If Table2 already exists, your syntax will change to be like what other answers indicated:

INSERT INTO Tabel2( IDTable1 )
SELECT
  IDTable1
FROM
  Table1

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18767

If you have only insertion, you can do this:

INSERT INTO Table2 (IDTable1)
SELECT IDTable1 FROM myTable1

It will select all IDTable1 from myTable1 and insert them into Table2.

Upvotes: 3

Related Questions