Insert the rows depending on the cell value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert the rows depending on the cell value

Author

Topic

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-04 : 08:55:23

I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.

Is it possible in TSQL?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 09:09:47

Yes, there are a couple options. One would be to compare a row_number() value to [blabla] in your WHERE criteria of the statment that produces your INSERTed rows.

You'll need to post some table structures, sample data, and expected results for a specific example...

Be One with the Optimizer
TG

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-04 : 09:44:14

Example:

I have to create this table

******************
newiD ID Name
------------------
1 1 Rob
2 1 Rob
3 2 Nick
4 2 Nick
5 2 Nick
******************

from this one

****************
ID Name col1
----------------
1 Rob 02
2 Nick 03
****************

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 10:09:01

How's this?


declare @targetTable table (newid int identity(1,1), ID int, Name varchar(10))

declare @t table (ID int, Name varchar(10),  col1 int)
insert @t
select 1, 'Rob', 2 union all
select 2, 'Nick', 3 

insert @targetTable (ID, Name)
select t.ID
       ,t.Name
from   @t t
join   master..spt_values v
              on v.type = 'p'
              and v.number < t.col1

select * from @targetTable

output:
newid       ID          Name
----------- ----------- ----------
1           1           Rob
2           1           Rob
3           2           Nick
4           2           Nick
5           2           Nick

Be One with the Optimizer
TG

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-04 : 10:20:26

Thanks! It works!
But I have one more question, if I may:

If in the source table I have more columns, how can I loop thru them and add rows to the destination table for each column?

for example:

**********************
ID Name col1 col2 col3
----------------------
1 Rob 02 01 01
2 Nick 03 01 00
**********************

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 11:34:38

What is your desired output for the example above?
4 rows for both Rob and Nick? (2+1+1 and 3+1+0)?
if so:


declare @targetTable table (newid int identity(1,1), ID int, Name varchar(10))

declare @t table (ID int, Name varchar(10),  col1 int, col2 int, col3 int)
insert @t
select 1, 'Rob', 2, 1, 1 union all
select 2, 'Nick', 3, 1, 0

insert @targetTable (ID, Name)
select t.ID
       ,t.Name
from   @t t
join   master..spt_values v
              on v.type = 'p'
where  v.number < (t.col1+t.col2+t.col3)

Please post all your requirments in one question with clear expected results to avoid a lot of back and forth

Be One with the Optimizer
TG

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-04 : 11:51:55

Yes.

My output has to be: 4 rows for Rob and Nick(2+1+1 and 3+1+0).

In your example you you are making sum of the columns like this (t.col1+t.col2+t.col3)?
But is it possible to make the sum of the columns dynamicly and without column names?

Thank you in advance,

Nick

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 12:49:22

quote:
But is it possible to make the sum of the columns dynamicly and without column names?

quote:
Please post all your requirments in one question with clear expected results to avoid a lot of back and forth

Here's how to avoid the back and forth:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-05 : 09:56:49

Sorry for incomprehensible question before.

This is what I have so far.

------------------------------------------------

declare @targetTable table (newid int identity(1,1), ID int, Name varchar(10), Age int)

declare @sourceTable table (ID int, Name varchar(10), age001 int, age002 int, age003 int)
insert @sourceTable
select 1, 'Saab', 1, 0, 1 union all
select 2, 'Volvo', 1, 1, 2

insert @targetTable (ID, Name)
select t.ID
,t.Name
from @sourceTable t
join master..spt_values v
on v.type = 'p'
where v.number < (t.age001+t.age002+t.age003)
select * from @sourceTable
select * from @targetTable

----------------------------------

What I need is to fill the "Age" column in the targetTable with values from my source table.
It has to be "1" for each row which is created from the source row if in this row @t.age001<>0. (depending on column name)
It has to be "2" for each row which is created from the source row if @t.age002<>0. And so forth.

This is expected result I need:

newid ID Name Age
---------------------------
1 1 Saab 1
2 1 Saab 3
3 2 Volvo 1
4 2 Volvo 2
5 2 Volvo 3
6 2 Volvo 3

Thanks in advance,

Nick

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-05 : 17:08:31

Anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 02:34:02

quote:
Originally posted by nick.dryaev

Sorry for incomprehensible question before.

This is what I have so far.

------------------------------------------------

declare @targetTable table (newid int identity(1,1), ID int, Name varchar(10), Age int)

declare @sourceTable table (ID int, Name varchar(10), age001 int, age002 int, age003 int)
insert @sourceTable
select 1, 'Saab', 1, 0, 1 union all
select 2, 'Volvo', 1, 1, 2

insert @targetTable (ID, Name)
select t.ID
,t.Name
from @sourceTable t
join master..spt_values v
on v.type = 'p'
where v.number < (t.age001+t.age002+t.age003)
select * from @sourceTable
select * from @targetTable

----------------------------------

What I need is to fill the "Age" column in the targetTable with values from my source table.
It has to be "1" for each row which is created from the source row if in this row @t.age001<>0. (depending on column name)
It has to be "2" for each row which is created from the source row if @t.age002<>0. And so forth.

This is expected result I need:

newid ID Name Age
---------------------------
1 1 Saab 1
2 1 Saab 3
3 2 Volvo 1
4 2 Volvo 2
5 2 Volvo 3
6 2 Volvo 3

Thanks in advance,

Nick

Not quite sure with your business rules. Can you explain how you think last row will have age 3?

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-06 : 03:31:27

This is my source table:

ID Name Age001 Age002 Age003
1 Saab 1 0 1
2 Volvo 1 1 2

For Volvo it means that there is one Volvo with age 1 (because we have 1 in column Age001), one Volvo with age 2 (because we have 1 in column Age002) and two Volvos with age 3 (because we have 2 in column Age003).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 04:04:40

[code]declare @targetTable table (newid int identity(1,1), ID int, Name varchar(10), Age int)

declare @sourceTable table (ID int, Name varchar(10), age001 int, age002 int, age003 int)
insert @sourceTable
select 1, 'Saab', 1, 0, 1 union all
select 2, 'Volvo', 1, 1, 2 union all
select 3, 'BMW', 1, 1, 0 union all
select 4, 'suzuki', 1, 0,0 union all
select 5, 'benz', 1, 2, 2 union all
select 6, 'gm', 2, 2, 0

insert @targetTable (ID, Name,Age)
select s.ID,s.Name,s.val from
(select ID,Name,age001 as Age,1 as val from @sourceTable
union all
select ID,Name,age002 as Age,2 as val from @sourceTable
union all
select ID,Name,age003 as Age,3 as val from @sourceTable)s
cross join master..spt_values v
where v.type='p'
and v.number <=s.Age
and s.Age>0
and v.number >0
order by s.ID,s.val

select * from @sourcetable

select * from @targetTable
output
----------------------------------------------------
sourcetable
--------------------
ID Name age001 age002 age003
----------- ---------- ----------- ----------- -----------
1 Saab 1 0 1
2 Volvo 1 1 2
3 BMW 1 1 0
4 suzuki 1 0 0
5 benz 1 2 2
6 gm 2 2 0
targettable
--------------
newid ID Name Age
----------- ----------- ---------- -----------
1 1 Saab 1
2 1 Saab 3
3 2 Volvo 1
4 2 Volvo 2
5 2 Volvo 3
6 2 Volvo 3
7 3 BMW 1
8 3 BMW 2
9 4 suzuki 1
10 5 benz 1
11 5 benz 2
12 5 benz 2
13 5 benz 3
14 5 benz 3
15 6 gm 1
16 6 gm 1
17 6 gm 2
18 6 gm 2[/code]

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-06 : 08:36:11

Thanks a lot!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources