Hello everyone, I am having trouble creating a stored procedure. I need this procedure to take a query and insert the results into a new table. Here is the requirements:
I want to take this query: SELECT DISTINCT REUNION_CLASS from PERSON_LS and insert all of these values into a column in a new table called ReunionDim, this table must also have an auto-generated unique id column.
Also, Is there anyway to set this up to take any table and any field value to make it into a generic table. like if I have to do the same thing with SELECT DISTINCT PROGRAM FROM PROGRAM_LS into a ProgramDim table?
Please help! I am doing this so I can make my data "cube-ready" for analysis and reporting! Thank you!
Here is what I have so far but it does not populate the table :(
If you really want to make the procedure generic, you need to do some serious string concatenation and use sp_executeSQL to perform the insert. To give you something to start with i give you a basic sample
This little code will copy 1 column from a table into a new table, but you probably would like to specify a complete list of source and target columns.
I am involved in a DataWareHouse project where we have built a set of procedures to get all meta data (tables, columns and column types) from different types of source systems (by linked servers to Oracle, Sybase and SQL server), store the meta data in some tables and based on the meta data we define the what data we would like and if we need some conversion. Then based on the meta data the procedures apply any changes to the target staging tables and imports the data into the staging tables.
It's very flexible as any new objects in the source system is automatically fetched into the staging area. If an object in the source is dropped the meta data information is marked as disabled.
It took a while to create these meta data procedures and you have to consider if it's worth the effort.
answered Jul 31, 2012 at 02:26 PM
The way you have it now, it's only going to generate a single NEWID() value. Also, this is a situation where you really don't need, or want a cursor. How about some thing like this:
If you still want to delete the record before inserting it, look at using the MERGE statement, detailed here: http://msdn.microsoft.com/en-us/library/bb510625.aspx
As to making this generic so you can pass in any set of tables, I wouldn't recommend it. T-SQL is a scripting language, not a full-blown programming language. It doesn't lend itself well to attempts at code reuse.