hello
Does exist in SQL language "for" loop ? If yes, what syntax does it has ?
best wishes
Adam 9 162537
No - see WHILE and "Cursors" in Books Online. But in SQL, a loop is
often not a good solution - people may use them when they should use a
single, set-based statement such as UPDATE or DELETE.
Of course, there are times when a while or cursor loop is the best
solution, so you might have a good reason to do this - if you can give
more details of what you need to achieve, someone may be able to
comment on whether a loop is appropriate for you or not.
Simon
Mostly it pays to find a set based solution rather than write loops. If
you need to loop then TSQL has WHILE.
--
David Portas
SQL Server MVP
--
I try to substitute "for" loop by this excample:
declare @i int
while (@i<10)
begin
insert into dbname.dbo.tabl e_name values @i
@i=@i+1
end
but in line @i=@i+1 ocured an error. Why ?
I know - it is stupid reason to use "for" loop , but what shoul I changed to
work this example ?
What are better ways to do such task ?
A common solution is to keep a table of numbers in your database
(single column of numbers from 0 to some very large number). This can
help avoid loops in many places:
INSERT INTO table_name (...)
SELECT num, ...
FROM Numbers
WHERE num BETWEEN 1 AND 10
Of course you'll still probably use a loop to populate the Numbers
table, but that only has to be done once and at install time, not at
runtime.
--
David Portas
SQL Server MVP
--
Numbers table:
CREATE TABLE numbers (num INTEGER NOT NULL CONSTRAINT pk_numbers
PRIMARY KEY) ;
INSERT INTO numbers (num) VALUES (0) ;
WHILE (SELECT MAX(num) FROM numbers)<65535
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;
--
David Portas
SQL Server MVP
--
Hi David,
We can do this simple trick
Select identity(int,1, 1) MyId into MyTable from
anySystemTable, anySystemTable, anySystemTable .......
With warm regards
Jatinder Singh
adam (er******@wp.pl ) writes: I try to substitute "for" loop by this excample:
declare @i int while (@i<10) begin insert into dbname.dbo.tabl e_name values @i @i=@i+1 end
but in line @i=@i+1 ocured an error. Why ?
You need to say SET or SELECT in front. I prefer SELECT, as you can assign
more that one variable in one bang:
SELECT @a = 1, @b = @c + d, @t = NULL
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Here is a way to gemerate the Sequence table without proprietary code
and create the rows in parallel instead of one at a time.
WITH
(SELECT 0
UNION ALL
SELECT 1
..
UNION ALL
SELECT 9) AS Digits(digit)
SELECT D1.digit + 10*D2.digit + 100*D3.digit + 1000*D4.digit + ..
FROM Digits AS D1, Digits AS D2, Digits AS D3, Digits AS D4, ..
WHERE (D1.digit + D2.digit + D3.digit + D4.digit + ..) > 0;
IDENTITY has be done one at a time, while this can be parallelized and
can generate numbers in sets. The CTE can be repalced with a TABLE or
VIEW. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Invalid User |
last post by:
While trying to print a none empty list, I accidentaly put an "else"
statement with a "for" instead of "if". Here is what I had:
if ( len(mylist)> 0) :
for x,y in mylist:
print x,y
else:
print "Empty list"
which was supposed to be:
|
by: deko |
last post by:
This sub pulls Outlook Appointments into a table.
The problem is I want to limit the import to Location = Boston
I'm not sure how to code that into the For... To loop - As it is now, I get
the following error:
Run-time error '3163':
The field is too small to accept the amount of data you attempted to add.
Try inserting less data.
|
by: songie D |
last post by:
would it be possible to sort of engineer some sort of preprocessor macro
that does a 'for' loop.
i.e. for where you would normally use a normal for loop, but when it is
known ay compile time whay the variable is, hence unrolling the loop.
|
by: Robbie Hatley |
last post by:
I'm getting a bizarre error with this code:
...
case WM_COMMAND:
{
switch (LOWORD(wParam)) // switch (control ID)
{
...
case IDC_RAIN_ENABLE_SIMPLE:
{
|
by: Fabian Vilers |
last post by:
Hi again...
I'm wondering what could be better in terms of performance between:
var my_array = new Array();
// populate array
for (index in my_array)
{
// do something with my_array
| |
by: s.lipnevich |
last post by:
Hi All,
I apologize if this was brought up before, I couldn't find any "prior
art" :-).
On more than one occasion, I found myself wanting to use a "conditional
loop" like this (with "Invalid syntax" error, of course):
for i in c if <test>:
print i*2
|
by: Frederick Gotham |
last post by:
Is the domestic usage of the C "for" loop inefficient when it comes to
simple incrementation? Here's a very simple program that prints out the
bit-numbers in a byte.
#include <stdio.h>
#include <limits.h>
#include <stdlib.h>
int main(void)
{
|
by: MDR |
last post by:
Hello
I have three "for" loops, two nested into the outer one and they depend on each other, like this:
for (x=1; x<100; x++)
{
....
for (i=1; i<10; i++)
{....}
for (j=1; j<10; j++)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |