its possible? 

For each id in (SELECT * FROM table)
     INSERT INTO table2(id,fk,nombre)
        values(1,id,'xxx');

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

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

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

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.

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.