Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1931

Re: WHILE LOOP AS FOR LOOP

Show all headers | View raw

On Wednesday, 29 July 2015 16:45:58 UTC+1, Erland Sommarskog  wrote:
> 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.

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) 
      SELECT 'S',id, 1 
      FROM table2 

and the identity column gets a key value automatically.

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

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

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, 
created, started (OPEN), stopped (CLOSE), and 
disposed of (DEALLOCATE) in the correct order, 
which is tricky.  So I like to use a "cursor 
variable"; when the variable ceases to exist, 
so does its cursor.  But some keywords don't work
with the cursor variable - STATIC works but 
INSENSITIVE doesn't, I found recently.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

WHILE LOOP AS FOR LOOP Jessica González <jess.rgm@gmail.com> - 2015-07-29 06:58 -0700
  Re: WHILE LOOP AS FOR LOOP Erland Sommarskog <esquel@sommarskog.se> - 2015-07-29 14:04 +0000
    Re: WHILE LOOP AS FOR LOOP Jessica González <jess.rgm@gmail.com> - 2015-07-29 07:31 -0700
      Re: WHILE LOOP AS FOR LOOP Erland Sommarskog <esquel@sommarskog.se> - 2015-07-29 15:44 +0000
        Re: WHILE LOOP AS FOR LOOP rja.carnegie@gmail.com - 2015-07-29 10:03 -0700
  Re: WHILE LOOP AS FOR LOOP --CELKO-- <jcelko212@earthlink.net> - 2015-08-14 17:54 -0700

csiph-web