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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)