ROWTYPE insert

[#1]

Is there a way to insert a ROWTYPE?
I thought something like this would work but it does not:
DECLARE @mtRow MyTable%ROWTYPE;
SELECT * INTO @mtRow FROM MyTable where ID=0;
INSERT INTO MyTable values (@mtRow);

[fvestjens]

I don't know what you're trying to achieve, but this is another solution of what you're trying:

DECLARE LOCAL TEMPORARY TABLE @tblMT like MyTable;
INSERT INTO @tblMT SELECT * FROM MyTable WHERE ID = 0;
INSERT INTO MyTable SELECT * FROM @tblMT

Another solution could be a stored procedure and passing MyTable and @mtRow as parameters and create an execute immediate statement to insert the data from @mtRow in MyTable

[VolkerBarth]

I do agree, "SELECT INTO ..." / "INSERT SELECT FROM" are the easiest means to do schema-agnostic copies of complete rows between tables, and with AUTO NAME you can even adjust some column names.