|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
insert records dependent on values from other table - with a logiccan somebody show me please how to solve this structure of the first table id (primary key) f1 (varchar field) nr (numeric field with values from 1 to 100) structure second table id2(autonumber) id (integer value ) f2 (varchar field) the value from table 1 have to be inserted in table 2 with a logic like in the example example table1 id field1 field2 ...... 11 a 2 12 b 4 13 c 1 ..... i have to insert values dependent on the first table table2 1 11 a1 2 11 a2 3 12 b1 4 12 b2 5 12 b3 6 12 b4 7 13 c1 the rule is that i must insert a nr of records depening on the numeric field value of table 1. if numeric value for example is 3 - there must be added 3 records and the field in table 2 must be set to a value fielvaluetabel +1 fielvaluetabel +2 fielvaluetabel +3 thanks Hi
Do u want to update a table-2 or insert records in it? -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://www.SQLResource.com/ --------------------------------------- "Xavier" wrote: > hello, > > can somebody show me please how to solve this > > structure of the first table > id (primary key) > f1 (varchar field) > nr (numeric field with values from 1 to 100) > > structure second table > id2(autonumber) > id (integer value ) > f2 (varchar field) > > the value from table 1 have to be inserted in table 2 with a logic like in > the example > > example > table1 > id field1 field2 > ..... > 11 a 2 > 12 b 4 > 13 c 1 > .... > i have to insert values dependent on the first table > table2 > > 1 11 a1 > 2 11 a2 > 3 12 b1 > 4 12 b2 > 5 12 b3 > 6 12 b4 > 7 13 c1 > > the rule is that i must insert a nr of records depening on the numeric field > value of table 1. > if numeric value for example is 3 - there must be added 3 records > and the field in table 2 must be set to a value > fielvaluetabel +1 > fielvaluetabel +2 > fielvaluetabel +3 > > thanks > > only a insert
best regards Show quote "Chandra" wrote: > Hi > > Do u want to update a table-2 or insert records in it? > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://www.SQLResource.com/ > --------------------------------------- > > > > "Xavier" wrote: > > > hello, > > > > can somebody show me please how to solve this > > > > structure of the first table > > id (primary key) > > f1 (varchar field) > > nr (numeric field with values from 1 to 100) > > > > structure second table > > id2(autonumber) > > id (integer value ) > > f2 (varchar field) > > > > the value from table 1 have to be inserted in table 2 with a logic like in > > the example > > > > example > > table1 > > id field1 field2 > > ..... > > 11 a 2 > > 12 b 4 > > 13 c 1 > > .... > > i have to insert values dependent on the first table > > table2 > > > > 1 11 a1 > > 2 11 a2 > > 3 12 b1 > > 4 12 b2 > > 5 12 b3 > > 6 12 b4 > > 7 13 c1 > > > > the rule is that i must insert a nr of records depening on the numeric field > > value of table 1. > > if numeric value for example is 3 - there must be added 3 records > > and the field in table 2 must be set to a value > > fielvaluetabel +1 > > fielvaluetabel +2 > > fielvaluetabel +3 > > > > thanks > > > > Xavier
Try this, If its ok for you to use cursors SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER proc Myproc as DECLARE @ID int, @F1 varchar(20),@f2 varchar(10), @NR INT, @COUNTER INT DECLARE Mycursor CURSOR READ_ONLY FOR SELECT [ID],f1,nr FROM TABLE1 OPEN Mycursor FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN select @COUNTER = 1 WHILE (@NR > (@COUNTER - 1)) BEGIN select @f2 = @F1 + CAST(@COUNTER AS VARCHAR(10)) INSERT INTO TABLE1([ID],F2) VALUES(@ID,@f2) SELECT @COUNTER = @COUNTER + 1 END END FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR END CLOSE Mycursor DEALLOCATE Mycursor GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Regards R.D --Post back if you want something less of cursors Show quote "Xavier" wrote: > hello, > > can somebody show me please how to solve this > > structure of the first table > id (primary key) > f1 (varchar field) > nr (numeric field with values from 1 to 100) > > structure second table > id2(autonumber) > id (integer value ) > f2 (varchar field) > > the value from table 1 have to be inserted in table 2 with a logic like in > the example > > example > table1 > id field1 field2 > ..... > 11 a 2 > 12 b 4 > 13 c 1 > .... > i have to insert values dependent on the first table > table2 > > 1 11 a1 > 2 11 a2 > 3 12 b1 > 4 12 b2 > 5 12 b3 > 6 12 b4 > 7 13 c1 > > the rule is that i must insert a nr of records depening on the numeric field > value of table 1. > if numeric value for example is 3 - there must be added 3 records > and the field in table 2 must be set to a value > fielvaluetabel +1 > fielvaluetabel +2 > fielvaluetabel +3 > > thanks > > thats great, all looks fine when the input data are from a table or view.
My data comes from a select statement from table1 for example: SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order by InvoiceId how can i use this as a input. best regards Xavier Show quote "R.D" wrote: > Xavier > Try this, If its ok for you to use cursors > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS ON > GO > ALTER proc Myproc > as > DECLARE @ID int, > @F1 varchar(20),@f2 varchar(10), > @NR INT, > @COUNTER INT > DECLARE Mycursor CURSOR > READ_ONLY > FOR SELECT [ID],f1,nr FROM TABLE1 > OPEN Mycursor > FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR > WHILE (@@fetch_status <> -1) > BEGIN > IF (@@fetch_status <> -2) > BEGIN > select @COUNTER = 1 > WHILE (@NR > (@COUNTER - 1)) > BEGIN > select @f2 = @F1 + CAST(@COUNTER AS VARCHAR(10)) > INSERT INTO TABLE1([ID],F2) VALUES(@ID,@f2) > SELECT @COUNTER = @COUNTER + 1 > END > END > FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR > END > CLOSE Mycursor > DEALLOCATE Mycursor > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > Regards > R.D > --Post back if you want something less of cursors > > > "Xavier" wrote: > > > hello, > > > > can somebody show me please how to solve this > > > > structure of the first table > > id (primary key) > > f1 (varchar field) > > nr (numeric field with values from 1 to 100) > > > > structure second table > > id2(autonumber) > > id (integer value ) > > f2 (varchar field) > > > > the value from table 1 have to be inserted in table 2 with a logic like in > > the example > > > > example > > table1 > > id field1 field2 > > ..... > > 11 a 2 > > 12 b 4 > > 13 c 1 > > .... > > i have to insert values dependent on the first table > > table2 > > > > 1 11 a1 > > 2 11 a2 > > 3 12 b1 > > 4 12 b2 > > 5 12 b3 > > 6 12 b4 > > 7 13 c1 > > > > the rule is that i must insert a nr of records depening on the numeric field > > value of table 1. > > if numeric value for example is 3 - there must be added 3 records > > and the field in table 2 must be set to a value > > fielvaluetabel +1 > > fielvaluetabel +2 > > fielvaluetabel +3 > > > > thanks > > > > Just change the FOR SELECT statement in the cursor
That should work Regards R.D Show quote "Xavier" wrote: > thats great, all looks fine when the input data are from a table or view. > > My data comes from a select statement from table1 > > for example: > SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order > by InvoiceId > > how can i use this as a input. > > best regards > Xavier > > "R.D" wrote: > > > Xavier > > Try this, If its ok for you to use cursors > > SET QUOTED_IDENTIFIER ON > > GO > > SET ANSI_NULLS ON > > GO > > ALTER proc Myproc > > as > > DECLARE @ID int, > > @F1 varchar(20),@f2 varchar(10), > > @NR INT, > > @COUNTER INT > > DECLARE Mycursor CURSOR > > READ_ONLY > > FOR SELECT [ID],f1,nr FROM TABLE1 > > OPEN Mycursor > > FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR > > WHILE (@@fetch_status <> -1) > > BEGIN > > IF (@@fetch_status <> -2) > > BEGIN > > select @COUNTER = 1 > > WHILE (@NR > (@COUNTER - 1)) > > BEGIN > > select @f2 = @F1 + CAST(@COUNTER AS VARCHAR(10)) > > INSERT INTO TABLE1([ID],F2) VALUES(@ID,@f2) > > SELECT @COUNTER = @COUNTER + 1 > > END > > END > > FETCH NEXT FROM Mycursor INTO @ID,@F1,@NR > > END > > CLOSE Mycursor > > DEALLOCATE Mycursor > > GO > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS ON > > GO > > Regards > > R.D > > --Post back if you want something less of cursors > > > > > > "Xavier" wrote: > > > > > hello, > > > > > > can somebody show me please how to solve this > > > > > > structure of the first table > > > id (primary key) > > > f1 (varchar field) > > > nr (numeric field with values from 1 to 100) > > > > > > structure second table > > > id2(autonumber) > > > id (integer value ) > > > f2 (varchar field) > > > > > > the value from table 1 have to be inserted in table 2 with a logic like in > > > the example > > > > > > example > > > table1 > > > id field1 field2 > > > ..... > > > 11 a 2 > > > 12 b 4 > > > 13 c 1 > > > .... > > > i have to insert values dependent on the first table > > > table2 > > > > > > 1 11 a1 > > > 2 11 a2 > > > 3 12 b1 > > > 4 12 b2 > > > 5 12 b3 > > > 6 12 b4 > > > 7 13 c1 > > > > > > the rule is that i must insert a nr of records depening on the numeric field > > > value of table 1. > > > if numeric value for example is 3 - there must be added 3 records > > > and the field in table 2 must be set to a value > > > fielvaluetabel +1 > > > fielvaluetabel +2 > > > fielvaluetabel +3 > > > > > > thanks > > > > > > On Wed, 7 Sep 2005 22:55:01 -0700, Xavier wrote:
Show quote >hello, Hi Xavier,> >can somebody show me please how to solve this > >structure of the first table >id (primary key) >f1 (varchar field) >nr (numeric field with values from 1 to 100) > >structure second table >id2(autonumber) >id (integer value ) >f2 (varchar field) > >the value from table 1 have to be inserted in table 2 with a logic like in >the example > >example >table1 >id field1 field2 >..... >11 a 2 >12 b 4 >13 c 1 >.... >i have to insert values dependent on the first table >table2 > >1 11 a1 >2 11 a2 >3 12 b1 >4 12 b2 >5 12 b3 >6 12 b4 >7 13 c1 > >the rule is that i must insert a nr of records depening on the numeric field >value of table 1. >if numeric value for example is 3 - there must be added 3 records >and the field in table 2 must be set to a value > fielvaluetabel +1 > fielvaluetabel +2 > fielvaluetabel +3 > >thanks > This is trivial if you have a table of numbers. Making it is quite easy, and a one time job to boot (just remember never to drop the table again). A numbers table has lots of other uses as well. Here's an article that describes how to make a table of numbers and several uses for it: http://www.aspfaq.com/show.asp?id=2516 And here's how to solve your problem with a numbers table: INSERT table2 (id, f2) SELECT t.id, t.f1 + STR(n.Number, 1) FROM table1 AS t1 JOIN Numbers AS n ON n.Number <= t1.nr (untested) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics