February 23, 2007 at 4:18 am
Is there anyway of specifying a table on the exec command line of a stored proc to run all the data that the sp would normally return directly into a table?
February 23, 2007 at 4:49 am
Adam
You can create a table with the same columns as the output of the stored procedure, then insert the results into it like this:
INSERT INTO MyTable ( <column list> ) EXEC MyProc
John
February 23, 2007 at 6:39 am
Thanks John, so you have to create a table first? There is no equiverlant to this;
SELECT <column list> INTO <Table Name>?
February 23, 2007 at 7:00 am
Adam
No. Books Online tells us that for the SELECT statement, the select list is as follows:
< select_list > ::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [ AS ] column_alias ]
| column_alias = expression
} [ ,...n ]
Whereas, for the INSERT statement, the execute statement is explicitly mentioned:
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply