April 27, 2012 at 9:25 am
Hi,
I have a table named test1 which as the following fields:
Number
reference
Quantity
UnitPrice
Supplier
.....
.....
I want to insert into other table the products one by one. Example:
Test1 data:
1; 00025#; 4; 250, Pedro
2; 00027#; 10; 40; Marco
I would like that data be inserted into test2 like:
00025#; 250
00025#; 250
00025#; 250
00025#; 250
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
00027#;40
As you can see, for each reference I have the number of products that I would like to insert into the other table.
I would like to make a procedure that reads the data from table test1 and insert's it into teste 2 as in the example.
Do I need to make a cursor for this?
April 27, 2012 at 9:36 am
why do you want to do this one by one ?
MVDBA
April 27, 2012 at 9:38 am
because each one Is a product.
April 27, 2012 at 9:43 am
That doesn't answer the question.
Why do you need to insert one row at a time rather than inserting all the rows in one operation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 9:45 am
Is this a one time operation or something run regularly? If it's the former, I might do a cursor and let it look inside based on the number of products.
If it's the latter, is it happening when the rows are added to this table?
April 27, 2012 at 9:46 am
for example
why does the following not work for you ?
insert into table2 (Number,
reference,
Quantity,
UnitPrice,
Supplier)
select
Number
reference
Quantity
UnitPrice
Supplier
from table 1
where .....
this is the standard syntax for copying data from one table to another
MVDBA
April 27, 2012 at 9:47 am
It is a thing that will happens sometimes in a day.
Cursor is bad because of performance
April 27, 2012 at 9:47 am
The same question applies. If the quantity determines the number of INSERTs you could cross join to a tally table and insert the values from the first table a number of times where N<= Quantity.
INSERT Test2(Reference,UnitPrice)
SELECT
Reference,
UnitPrice
FROM
Test1 a, Tally b
WHERE
b.N <= a.Quantity
April 27, 2012 at 9:48 am
Steve Jones - SSC Editor (4/27/2012)
Is this a one time operation or something run regularly? If it's the former, I might do a cursor and let it look inside based on the number of products.If it's the latter, is it happening when the rows are added to this table?
You did read my message well
April 27, 2012 at 9:49 am
declare @Table table (name varchar(15),cnt int,value int,xString varchar(16))
insert into @Table values ('00025#',4,250,'Pedro'),
('00027#',10,40,'Marco')
SELECT
n.RowNum,
Image.name
into Test2 --initial insert
FROM (
SELECT name,cnt
FROM @Table
) Image
CROSS APPLY (
SELECT TOP (cnt) RowNum = ROW_NUMBER() OVER(ORDER BY a.[Name])
FROM sys.columns a, sys.columns b
) n
LEFT JOIN @Table s ON s.cnt = n.RowNum
Cursors are Bad 🙂
April 27, 2012 at 9:54 am
I am thinking recursive CTE, something like
create table #tempstore(
id int , counts int , texts char(1)
)
insert into #tempstore
select 1 , 4 , 'a'
union all
select 2,5,'b'
;
with cte as (select id , counts , texts from #tempstore
union all
select c.id , c.counts-1 , c.texts from cte c
inner join #tempstore t
on c.counts >1
and c.id = t.id
)
select * from cte
order by id desc
drop table #tempstore
April 27, 2012 at 11:04 am
Thinky Night (4/27/2012)
declare @Table table (name varchar(15),cnt int,value int,xString varchar(16))insert into @Table values ('00025#',4,250,'Pedro'),
('00027#',10,40,'Marco')
SELECT
n.RowNum,
Image.name
into Test2 --initial insert
FROM (
SELECT name,cnt
FROM @Table
) Image
CROSS APPLY (
SELECT TOP (cnt) RowNum = ROW_NUMBER() OVER(ORDER BY a.[Name])
FROM sys.columns a, sys.columns b
) n
LEFT JOIN @Table s ON s.cnt = n.RowNum
Cursors are Bad 🙂
Nice! I like this one.
I only suggest the cursor for a quick, one-off execution, but then again, I don't use the Tally Table often enough.
April 30, 2012 at 7:39 am
It does not work....
I can not use select into in the cloud (SQL Zure) see error bellow, please:
Statement 'SELECT INTO' is not supported in this version of SQL Server.
April 30, 2012 at 7:47 am
Let's try it with CREATE TABLE and INSERT Statement. I only wanted to show as sample like that.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply