Can anyone help me with this INSERT SQL statement?

I have a table like the following:

owner Pet
__________
Bob   Joe
Sam   Rob
Bob   George
Phil  Marge

Now, I want to create an INSERT statement for a new Owner, Nick. I want to make my ins开发者_开发技巧ert statement such that for every pet that Bob owns, Nick owns one of the same name.

I'm trying to do this to accomplish my task:

INSERT INTO TABLE
VALUES ('Nick', Pet)
SELECT Pet FROM Table T
WHERE T.owner = 'Bob')

But the above isn't working. Can anyone please tell me what I'm doing wrong?

You were close:

INSERT INTO TABLE (owner, Pet)
SELECT 'Nick', Pet FROM Table WHERE owner = 'Bob'

I think this will do it.

Insert Into Table (Pet, Owner)
Select Pet, 'Nick' From Table Where Owner = 'Bob'

this will do what you want:

create table t_test3 ( ownr varchar(20) not null, pet varchar(20) not null);
insert into t_test3 (ownr, pet) values ('bob', 'joe');
insert into t_test3 (ownr, pet) values ('sam', 'rob');
insert into t_test3 (ownr, pet) values ('bob', 'george');
insert into t_test3 (ownr, pet) values ('phil', 'marge');

insert into t_test3 
     select 'nick', pet from t_test3 where ownr = 'bob';

select * from t_test3;

store pet inna var and use in second query.

SELECT Pet INTO @PET FROM Table WHERE owner = 'Bob')
INSERT INTO TABLE VALUES ('Nick', @PET)

Thats what you want?

INSERT Pets (owner, Pet)
SELECT 'Nick', Pet
FROM Pets
WHERE owner = 'Bob'

Assuming your table is named 'Pets'. Run the Select without the insert to see the rows that will be inserted first.

Can we make it even simpler?

SELECT 'Nick', pet INTO table FROM table WHERE Owner = 'Bob'