f



WHILE LOOP AS FOR LOOP

its possible? 

For each id in (SELECT * FROM table)
     INSERT INTO table2(id,fk,nombre)
        values(1,id,'xxx');
0
ISO
7/29/2015 1:58:07 PM
comp.databases.ms-sqlserver 14567 articles. 0 followers. janinepelletier (108) is leader. Post Follow

5 Replies
575 Views

Similar Articles

[PageSpeed] 17

Jessica Gonz�lez (jess.rgm@gmail.com) writes:
> its possible? 
> 
> For each id in (SELECT * FROM table)
>      INSERT INTO table2(id,fk,nombre)
>         values(1,id,'xxx');
> 

So in SQL, you don't normally code the loops explicitly - the loops are 
hidden on the inside. Logically, it is an operation of a set.

   INSERT table(id, fk, nombre)
      SELECT 1, id, 'xxx'
      FROM   table


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
7/29/2015 2:04:52 PM
thanks.. but

SET IDENTITY_INSERT table ON;
INSERT INTO table(pk,activo,permiso,rol) 
      SELECT IDENT_CURRENT('table')+1,'S',id, 1
      FROM table2


return error  

Violation of PRIMARY KEY constraint 'PK__table__3213E83F2B947552'. Cannot insert duplicate key in object 'table'. The duplicate key value is (6).

only 'id' i need get from table2
0
ISO
7/29/2015 2:31:23 PM
Jessica Gonz�lez (jess.rgm@gmail.com) writes:
> thanks.. but
> 
> SET IDENTITY_INSERT table ON;
> INSERT INTO table(pk,activo,permiso,rol) 
>       SELECT IDENT_CURRENT('table')+1,'S',id, 1
>       FROM table2
> 
> 
> return error  
> 
> Violation of PRIMARY KEY constraint 'PK__table__3213E83F2B947552'.
> Cannot insert duplicate key in object 'table'. The duplicate key value
> is (6). 

Why do you fiddle with IDENTITY_INSERT ON? That is an option you rarely have 
any reason to play with. And IDENT_CURRENT you have even more rare reason to 
touch. That is not a particularly useful function.

If you have made the column an IDENTITY column, you should not assign the 
values explicitly. And vice versa: if you want to be able to specify the 
values explicitly, you sould not use IDENTITY.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
7/29/2015 3:44:21 PM
On Wednesday, 29 July 2015 16:45:58 UTC+1, Erland Sommarskog  wrote:
> Jessica Gonz=E1lez (jess.rgm@gmail.com) writes:
> > thanks.. but
> >=20
> > SET IDENTITY_INSERT table ON;
> > INSERT INTO table(pk,activo,permiso,rol)=20
> >       SELECT IDENT_CURRENT('table')+1,'S',id, 1
> >       FROM table2
> >=20
> >=20
> > return error =20
> >=20
> > Violation of PRIMARY KEY constraint 'PK__table__3213E83F2B947552'.
> > Cannot insert duplicate key in object 'table'. The duplicate key value
> > is (6).=20
>=20
> Why do you fiddle with IDENTITY_INSERT ON? That is an option you rarely h=
ave=20
> any reason to play with. And IDENT_CURRENT you have even more rare reason=
 to=20
> touch. That is not a particularly useful function.
>=20
> If you have made the column an IDENTITY column, you should not assign the=
=20
> values explicitly. And vice versa: if you want to be able to specify the=
=20
> values explicitly, you sould not use IDENTITY.

Of course maybe you didn't make the column IDENTITY,
some other idiot did.

And maybe some idiot boss says that you have to assign
it explicitly.

Or, you just changed your mind.

But the point is that /usually/ you'd do this:

INSERT INTO table(activo,permiso,rol)=20
      SELECT 'S',id, 1=20
      FROM table2=20

and the identity column gets a key value automatically.

A different case to override it is if you want the=20
key in table 1 to hold the same value as the key=20
in table 2.  Sometimes you have to do a thing=20
like that.  But then you will just insert using the=20
key column name from table 2, which has a different=20
value in each row. =20

SET IDENTITY_INSERT table ON;=20
INSERT INTO table(pk,activo,permiso,rol)=20
      SELECT pk2,'S',id, 1=20
      FROM table2;
SET IDENTITY_INSERT table OFF;=20

If you do want to write a loop in Transact SQL
then a conventional way to do it is to use a "cursor",
an object that can fetch one table row at a time.
But this is not popular.

An ordinary cursor needs to be uniquely named,=20
created, started (OPEN), stopped (CLOSE), and=20
disposed of (DEALLOCATE) in the correct order,=20
which is tricky.  So I like to use a "cursor=20
variable"; when the variable ceases to exist,=20
so does its cursor.  But some keywords don't work
with the cursor variable - STATIC works but=20
INSENSITIVE doesn't, I found recently.
0
rja
7/29/2015 5:03:49 PM
Your mindset is wrong. SQL is a declarative language and does not use proce=
dural constructs. Things happen "all at once" in whole sets, not in sequent=
ial steps. This means we have no loops. A classic book on this topic is Pet=
er Henderson's FUNCTIONAL PROGRAMMING.
0
CELKO
8/15/2015 12:54:12 AM
Reply:

Similar Artilces:

loops in loops
In the attached vi, the one second loop runs at one second and can't be touched.  In the ten second loop, the ten second clock is a stand in for a process that generates a finished signal. The problem is once the process is complete, to start it again, leave the one second loop running, and start an additional process that will take less time than 10 seconds. thanks internal loops.vi: http://forums.ni.com/attachments/ni/170/319512/1/internal loops.vi Hi exo, what do you try there? If you start your vi, the value from your stop button will be read and after that, both loops &quo...

while loop in a while loop
Hi All, I have the following situation: while(rs.next()) { //loop1 while(rs2.next()) { //loop 2 } } It seems like loop 2 is only beeing used once. Should I after loop2 put the cursor back to the beginning? if so, how do I do that? Thanks! -- Posted by news://news.nb.nu Steven wrote: > Hi All, > > I have the following situation: > > > while(rs.next()) { //loop1 > > while(rs2.next()) { > //loop 2 > } > > } > The architypal loop (for a List eg ArrayList of Blah objects) is: for(It...

For loop while loop
I need to evaluate a function from 0:200 then create an array of every 5th value in the 0:200 array using a for loop then using a while loop. I can't get the for loop to work at all because I cannot use >,< controllers, only an =. So I focused on the While loop: n=0:200;%creates 201 cell array from 0-200 x=n.^2+.25;%calculates x plotcol=(length(n)-1)/5;%=(201-1)/5=40 y=zeros(1,plotcol);%creates 1X40 vector col=1;%used for incrementing in while loop while col<=plotcol; y(1,col)=x(1,5*col); col=col+1; end Unfortunately the output starts at 16 when it should start ...

FOR loop not looping
I have the below script that uses 2 for loops. for some reason they loop is not looping ! /bin/ksh CURR_PATH='/main/nedcor/flexcube' cd $CURR_PATH for DIR1 in 'WIP' 'INPUT';do echo $DIR1 for DIR in $(find . -name $DIR1);do RESULT=`ls -A $DIR | egrep -v "BKUP|BKP"` echo $DIR echo $RESULT echo $RESULT1 if [ -z "$RESULT" ] ; then echo "No Files found" exit 0 else echo file $RESULT found exit 1 ...

for loop into for loop.
Hello this is my first time that i am writing here.. I need your help to improve my code. This is my code for a=1:numberOfShortestPaths for b=a+1:a+numberOfShortestPaths for c=b+1:b+numberOfShortestPaths for d=c+1:c+numberOfShortestPaths for e=d+1:d+numberOfShortestPaths total_weight=weight(:,:,a)+weight(:,:,b)+weight(:,:,c)+weight(:,:,d)+weight(:,:,e); maximum=max(total_weight); end end end end end i want to do this function "automatically" because the letters ...

For loop over a while loop
Hi, I am computing a quantity that depends on two variables. The computation itself is iterative (i'm using 3 while loops to compute it). Now i want to do a sensitivity analysis of the input variables and do a 3D plot where I can start making sense out of my results, however not sure how to write a for loop over these while loops to compute different combinations of what I define as my parametric space for my two input variables. Thanks in advance, Vash Please add a code example and/or more specific question.... ...

Loop in a loop?
Hi, I'm new to Python and have come across a problem I don't know how to solve, enter com.lang.python :) I'm writing some small apps to learn the language, and I like it a lot so far. My problem I've stumbled upon is that I don't know how to do what I want. I want to do a loop in a loop. I think. I've got two arrays with some random stuff in, like this. array1 = ['one','two','three','four'] array2 = ['a','b','c','d'] I want to loop through array1 and add elements from array2 at the end, so it looks lik...

while loop in a while loop
Hi all, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I am trying to write a program with a while loop within another while loop. When I run the vi both while loops&nbsp; run but then only the inner while loop seems to update, I adjust controls and they have no effect on the indicators. &nbsp; Thanks.. You need to familiarize with the concept of dataflow programming. Run your VI in "execution highligthing" mode while watching the diagram and you'll get a better feeling on how things work. :) &nbsp; If both loops need to run in para...

loop not looping
This should be relatively easy for someone more experienced to pick out what I'm doing wrong here...All I want is my loop to run as many times as there are .dat files in the specified directory. I've hacked up my code a bit just so i can get it to work and realize it's not as simple or efficient as it could be. My script only runs through the loop once and I can't figure out why it never loads 'binary_002.dat' and so on. thanks for your help counter_value = 1; %counter value set back to 0 when while loop = false slice_thicknessMM = 1.5; tf = isdir('C:\Documen...

LOOP and +LOOP
With LOOP, since the index (I) increases by 1 with each loop iteration, we can test for equality as a means to determine if it's time to exit the loop or not: : TEST 10 0 DO I . LOOP ; TEST 0 1 2 3 4 5 6 7 8 9 Here, when I gets to 10, the loop exits. It's not so easy with +LOOP, since the index can be changed by any arbitrary value on each iteration: : TEST 100 0 DO I . RANDOM +LOOP ; Here, we cannot test for equality. We have to test for crossing a boundary instead, at least as far as I can see. With that in mind, are these following results correct: : TEST 10 0 DO I . 1 +LOOP ...

Having trouble stopping a loop (or loop de loop de loop)
Hi again everyone, Here is my rather clunky code (and I know it is rather inefficient): ------------------------------------------------------------------------------------------------------------------------------ while e_t lt 2 do begin for x=4e003, 20e003, 4e003 do begin for c=0.006, 0.01, 0.004 do begin for o=(50*!pi/180), (70*!pi/180), (10*!pi/180) do begin v= (a*x) ind_small = where(file[1,*] lt 2*x,count) if count eq 0 then te_small=0 if count gt 0 then begin ext_small = file[*,ind_small] le_small= ext_small[1,*] te_small = total(le_small^3) endif ind_large = where(file[1,*] ge...

Nested FOR loops. (a loop within a loop)
I can't get my head around them. I have two MySQL tables, one contains the categories, the other contains the posts. Each post is linked to a category. Now I'm trying to display each post with a drop down menu that has the categories in it and have the category that the post is in selected. Here's my code for the drop down menu where my woes lie. for ($i=0; $i<count($posts); $i++) { for ($j=1; $j<=count($categories); $j++) { if ($j == $post[$i]['id']) { selected = ' selected="selected"'; } else { sele...

what diference between timed loop and while loop and for loop
If I want to count 2 second working in the loop and next then going out of the loop I don't know&nbsp;which the loop &nbsp;I can use Message Edited by Jairak on 07-26-2008 03:14 PM Why do you need a loop to count for 2 seconds? To learn more about LabVIEW it is recommended that you go through the tutorial(s) and look over the material in the NI Developer Zone's <a href="http://zone.ni.com/devzone/cda/tut/p/id/5054" target="_blank">Learning Center</a> which provides links to other materials and other tutorials. You can also take the <a href=...

Triple nested loop python (While loop insde of for loop inside of while loop)
try to make my triple nested loop working. My code would be: c = 4 y1 = [] m1 = [] std1 = [] while c <24: c = c + 1 a = [] f.seek(0,0) for columns in ( raw.strip().split() for raw in f ): a.append(columns[c]) x = np.array(a, float) not_nan = np.logical_not(np.isnan(x)) indices = np.arange(len(x)) interp = interp1d(indices[not_nan], x[not_nan], kind = 'nearest') p = interp(indices) N = len(p) dt = 900.0 #Time step (seconds) fs = 1./dt #Sampling frequency KA,PSD = oned_Fourierspectrum(p,dt) # Call Song's 1D FS function time_axis = np.linspace(0.0,N,num = N,endpoint = False)*15/(60*24) plot_freq = 24*3600.*KA #Convert to cycles per day plot_period = 1.0/plot_freq # convert to days/cycle fpsd = plot_freq*PSD d = -1 while d <335: d = d + 1 y = fpsd[d] y1 = y1 + [y] m = np.mean(y1) m1 = m1 + [m] print m1 -------------------------------------------------------------------------------- My purpose is make a list of [mean(fpsd[0]), mean(fpsd[1]), mean(fpsd[2]).. mean(fpsd[335])]. Each y1 would be the list of fpsd[d]. I check it is working pretty well before second while l...

loops in loops= superloops
I have this huge loop in matlab, which of course asks a lot of computation time. I can't find another way because i need the differences between the steps in Q, and also in my formula, but I'm not a genius with matlab. can anyone give me advice on how to remove my loops? Thanks Adinda The loop: Hms=zeros(nx,ny,nz,3); Hmsnk=zeros(nx,ny,nz); gradHms=zeros(nx,ny,nz,3,6); %for loop with calculation of the magnetostatic field and its derivative for i=1:nx for j=1:ny for s=1:nz for k=1:nx for l=1:ny for m=1:nz ...

nested loops only loop once
I am using two while loops that are nested. The first loop (post name) returns the full column of results, but the second (post modified) only returns the first row of the column. Is there another way I could write this to get both loops to complete fully? I am using the two while loops to pull data from different tables, and insert that data into a list that has html code surrounding each loop. while ($url = mysql_fetch_array($urls, MYSQL_ASSOC)) { $pn = $url['post_name']; while ($date = mysql_fetch_array($lmdate, MYSQL_ASSOC)) { $lm = $date['post_modified']; ...

conditional loops (while loops)
I am using a while loop to check a digital input. I would like to send the value to a true or false box to check another digital input. The problem is that the while loop is running continuously but not giving anything out through the connection. I am looking for something like "if this do that conditional". Is there a way to set this up? Hello! The =93if this, do that conditional=94 tools in LabVIEW are the Select function (All Functions >> Comparison subpalette) and the Case Structure (All Functions >> Structures subpalette), depending on the complexity of what you wa...

while-loops nested in while-loop
Hi! I'm new to labview and I've got some problems. I want to program a control for a testing facility. therefore I want to program several while-loops&nbsp; nested in each other, according to a designed Nassi-Shneiderman-diagram. So if the stop condition for an inner while loop is true (e.g. measure device mistake), the program should jump out of that while loop in the next outer while-loop. is that possible? Thank you right now! Yes it is possible When you stop the inner loop, from inside the inner loop, just&nbsp;wire/pass an appropriate&nbsp;stop condition ( stop if tr...

Loop within a loop ???
Hi, I'm having trouble copying table data to new records. I have two tables as follows: *** Specifications (Table) specification_ID (field) LINKED product_ID (field) specification_header (field) ________________________________________ *** Specification_detail (Table) specification_detail_ID (field) specification_detail_text (field) specification_ID (field) LINKED specification_ID in this table is linked to specification_ID in Specifications. ________________________________________ On a form, related by product_ID, 'Specifications' fills a subform, no problem. When you cli...

Loop-Loop Calculation
Hi &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i have one VI in that i want to do Loop to Loop calculation. In my VI first the inner loop have to execute first . Loop-Loop calc.vi: http://forums.ni.com/attachments/ni/170/302256/1/Loop-Loop calc.vi I think you should explain better what you wean by "loop to loop calculation". What are you trying to accomplish? ...

While loop VS for loop
I am enabling the index and getting the values inside both for loop and while loop.Which one do you guys think is efficient and a proper coding style.I maybe wrong but i feel while loop to be much faster than for loop.Is that true? rpr wrote:i feel while loop to be much faster than for loop.Is that true? No, they both run at basically the same speed given the same conditions. If anything, the while loop will run a bit slower because it has a condition to evaluate with every iteration. &nbsp; The basic rule is simple - you use a for loop if you want to run a known number of times. Other...

For loop vs While loop
Hi all, For any counting operation what is the advantage of a while loop over a for loop. Internally how is it implemented which results in this advantage. Does it matter in applications where timing is very important like in embedded real time systems. Any inputs on the same are highly appreciated. Thanks and Regards, Aru On Mon, 24 Nov 2008 18:29:41 -0800 (PST), aruna.mysore@gmail.com wrote in comp.programming: > Hi all, > > For any counting operation what is the advantage of a while loop over > a for loop. Internally how is it Each of them is better than the other. > i...

stop an if loop in a for loop
Hello, I try to find a way to stop an if loop inside of a for loop but keep the for loop going. I tried to use "return" and "break" but they stop the for loop too. Bellow is a simple script to explain what I mean. Using "return", it stops the loop when z = 2 but I would like to find a way to keep the for loop going until z = 3. i = 1; for z = 1:3 i = i+1; if i ==3 return % Stop the if loop else display('Cant do it') end end Thank you for you help Benoit "Benoit " <igneb@iastate.edu> writes: > Hell...

while loop inside a for loop
Hi, can someone please tell me why "time" (t) is not updated correctly within the while loop? Please run this small code in your machine and you'll know what I'm taking about. What I want is: for t=1 run the "while loop" until iq<=(numberc-1) then t=2 run the "while loop" again until iq<=(numberc-1), and so on. Basically, this code runs through all the nonzero elements of A and attempts to divide them individually into two, once a certain time has passed. Please feel free to ask for clarifications. Many thanks in advance. A=[0 0 0 0 0 0 ...

Web resources about - WHILE LOOP AS FOR LOOP - comp.databases.ms-sqlserver

With All Eyes On Trump, Clinton Is Winning The Democratic Nomination
In South Carolina today, Hillary Clinton scored her biggest victory yet in the Democratic presidential primary. She beat Bernie Sanders by what ...

Clinton wins big in South Carolina on way to 'Super Tuesday'
By John Whitesides and Amanda Becker COLUMBIA, S.C. (Reuters) - Democratic presidential candidate Hillary Clinton rolled to a big victory over ...

Yoko Ono released from hospital after suffering flu symptoms
NEW YORK (AP) — Yoko Ono's son says his mother has been released from a New York City hospital after being treated for flu-like symptoms.

Warren Buffett defends 3G, touts economy in record year for Berkshire
Warren Buffett on Saturday used his annual letter to Berkshire Hathaway Inc shareholders to launch vigorous defenses of an aggressive private ...

Irish prime minister concedes defeat in election
Dublin (AFP) - Irish prime minister Enda Kenny on Saturday conceded defeat, as early indications from the election showed support for both his ...

Apple’s 4-inch iPhone Will Be Called The iPhone SE, Coming March 15, Will Cost Between $400-$500
While we have been reporting about the fact that Apple is planning to launch a new 4-inch version of its iPhone on March 15 event, there still ...

Kate Walsh & Sasha Alexander Celebrate Women in Film at Pre-Oscar Cocktail Party
Kate Walsh and Sasha Alexander meet up inside the 2016 Women in Film Pre-Oscar Cocktail Party held at Hyde Sunset on Friday night (February 26) ...

Syria fighting rages as cessation deadline nears
BEIRUT (Reuters) - Guns mostly fell silent in Syria and Russian air raids stopped on Saturday, the first day of a cessation of hostilities that ...

[PHOTOS] Spirit Awards Red Carpet Arrivals – Cate Blanchett and More - Variety
The Film Independent Spirit Awards will unfold Saturday afternoon in Santa Monica. See the best looks from the blue carpet.

Melissa Harris-Perry 'Highly Unlikely' To Return To MSNBC
"Melissa Harris-Perry" the TV show ended not with a celebration or a cancellation, but with a tug of war over Beyoncé. This feed and its contents ...

Resources last updated: 2/28/2016 2:09:59 AM