September 11, 2018 at 6:39 am
Hi
I'm not T-SQL/SP expert. Is it possible to write a small procedure that can copy the data/result set of a table, view or stored procedure to a new table in a different schema?
The procedure's parameters would probably look like this:
• Data Source / Type (Table, View, SP)
• Flag "Reload Table" (would drop/create the target table)
So if the Data Source Type is a table, it would simple copy its contents. If it's a view or stored procedure, it would select * / execute it.
For all three object types, it would add two metadata-fields to the target table: exportTS (sysdatetime) and exportUSR (system_user)
Thanks,
Roger
September 11, 2018 at 8:11 am
How about something like this?
SELECT column1, column2, column3, ...
INTO db.schema.newtable
FROM oldtable
WHERE condition;
September 11, 2018 at 10:03 am
rotcha99 - Tuesday, September 11, 2018 6:39 AMHi
I'm not T-SQL/SP expert. Is it possible to write a small procedure that can copy the data/result set of a table, view or stored procedure to a new table in a different schema?The procedure's parameters would probably look like this:
• Data Source / Type (Table, View, SP)
• Flag "Reload Table" (would drop/create the target table)So if the Data Source Type is a table, it would simple copy its contents. If it's a view or stored procedure, it would select * / execute it.
For all three object types, it would add two metadata-fields to the target table: exportTS (sysdatetime) and exportUSR (system_user)
Thanks,
Roger
Well, that's a handy tool, but how do you prevent SQL Injection? You're going to have to validate that your parameter values that specify an object name are indeed valid object names. Then you will have to decide, based on object type, what will need to be done. How do you plan on handling a Linked Server name that's part of an object name? Or will you choose to NOT handle it? Both are valid choices, but the former will require additional work to validate that the object exists on the Linked Server. Such could require permissions not currently in place... Lot's of thinking is needed to get this right and not open a gaping hole in security. Might want to severely limit who could execute such a procedure...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply