Create new row with a one new value based on value in another row

Question

What I have:

VALUE	PROPERTY	HOST
One	1	Dave
Two	2	Dave
Three	3	Dave

What I want to achieve:

VALUE	PROPERTY	HOST
One	1	Dave
Two	2	Dave
Three	3	Dave
One	1	Pete
Two	2	Pete
Three	3	Pete

So I'm trying to create new rows based on any rows that have Dave as the host. Those rows are identical to the Dave rows except the Host is Pete. The Dave rows are retained. The Pete rows are added.

Answer 1

Just use a VALUES table construct with both Dave and John in the data:

SELECT YT.Value,
       YT.Property,
       V.Host
FROM dbo.YourTable YT
     CROSS APPLY (VALUES('Dave'),
                        ('Pete'))V(Host)
WHERE YT.Host = 'Dave';

Answer 2

A simple INSERT INTO combined with a SELECT should do the trick.

INSERT INTO
  dbo.tablename (VALUE, PROPERTY, HOST)
SELECT
  VALUE, PROPERTY, 'Pete'
FROM
  dbo.tablename
WHERE
  HOST = 'Dave'