how do I loop through the following table and get it to insert records into another depending on the number that it finds in the value column. eg if there is 8 in the value column, 8 records should be inserted into another table with the same ID

i forgot to paste an example table

ID	Name	Value
1	john	12
2	sarah	20
3	Tom	5

I want it to look at the value column and if it is 12 it should add 12 rows with the same ID into another table. eg in the new table there will be 12 rows for John with an ID of 1. For Sarah there will be 20 rows with an ID of 2.

Hi Sukai

You could do this by two ways. link will help you ..

First way - INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.

USE AdventureWorks
 GO
 ----Create TestTable
 CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
 ----INSERT INTO TestTable using SELECT
 INSERT INTO TestTable (FirstName, LastName)
 SELECT FirstName, LastName
 FROM Person.Contact
 WHERE EmailPromotion = 2
 ----Verify that Data in TestTable
 SELECT FirstName, LastName
 FROM TestTable
 ----Clean Up Database
 DROP TABLE TestTable
 GO

Second way - SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

USE AdventureWorks
 GO
 ----Create new table and insert into table using SELECT INSERT
 SELECT FirstName, LastName
 INTO TestTable
 FROM Person.Contact
 WHERE EmailPromotion = 2
 ----Verify that Data in TestTable
 SELECT FirstName, LastName
 FROM TestTable
 ----Clean Up Database
 DROP TABLE TestTable
 GO
 

Thanks

Suhas

sorry this is not what i am looking for. Please read the message again.

thanks

You'll want to make use of a numbers table to do this:

DECLARE @sourceTable TABLE (ID INT, Name VARCHAR(20), Value INT)
INSERT INTO @sourceTable (ID, Name, Value) VALUES (1, 'john', 12),(2, 'sarah', 20),(3, 'Tom', 5)

DECLARE @numbers TABLE (value INT)
WHILE (SELECT COUNT(*) FROM @numbers) < (SELECT MAX(Value) FROM @sourceTable)
BEGIN
INSERT INTO @numbers (value) VALUES ((SELECT COUNT(*) FROM @numbers)+1)
END

Once you have that, just switch it to an insert:

INSERT INTO destinationTable
SELECT s.ID, s.Name, s.Value
  FROM @sourceTable s
    INNER JOIN @numbers n
	  ON s.Value >= n.value
 ORDER BY s.ID

• Edited by Patrick Hurst 16 hours 14 minutes ago

HI,

Try this:

DECLARE DemoCursor CURSOR FOR SELECT ID, Name, Value FROM YourTable;
DECLARE @cnt INT = 0;
OPEN DemoCursor
FETCH NEXT FROM DemoCursor INTO @ID, @Name, @Value
WHILE @@FETCH_STATUS = 0
BEGIN
  @cnt = 0;
  WHILE @cnt < @Value
  BEGIN
    Insert into Table (Col1, Col2) Values(@ID,@Name);
  END;
END;

More info on:

https://msdn.microsoft.com/en-us/library/ms180152.aspx

... it should add 12 columns with the same ID into another table.

Add COLUMNS? or rows? Presumably the latter, so imagine you have a table of numbers that run sequentially from 1 to 1000.  Now imagine that you join your table (and where is your script that we can all use to write and test queries?) to this <table of numbers> in some way.  Your value column contains the upper limit - 1 is the assumed lower limit.  The table contains a column with 1000 rows where the value of this column increases monotonically. How do you limit the join to get 12 rows for john and 20 rows for sarah (etc.)? 

You can find many suggestions for creating a table of number by searching.  The script (welcome) below contains a simple version.

DECLARE @number_of_numbers INT = 50;
declare @tbl table (id int, name varchar(20), value smallint); 

insert @tbl(id, name, value) values (1, 'john', 12), (2, 'sarah', 20), (3, 'tom', 5);


with numbers as (
	select 1 as num 
	union all 
	select num + 1 from numbers  where numbers.num <= 49
)
SELECT x.id, x.name, x.value, numbers.num
FROM @tbl as x inner join numbers 
on x.value >= numbers.num 
order by x.id, x.name, x.value, numbers.num
;

sorry, I made a mistake, it should say add 12 rows not columns.

I will not know the total number of rows in the table in advance. It should count the number of rows in the table.

All of these solutions are taking care of that already. You just need to create a table (or variable) holding a set of numbers. My solution populates that table with the maximum value from your table (20) as it won't need any more than that. A numbers table is a useful tool to have as a perminent fixture.

Once you have a numbers table you can join to it where your value is less than or equal to (<=) the value in the numbers table. This will cause a one to many relationship diticated by the value in your table (if your value is 5, it will make the join five times).

• Proposed as answer by Kalman TothModerator 12 hours 34 minutes ago

it should be rows and not columns

please can you comment on what each section of your code is doing.