asked
stored procedure to take data from query
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 :( USE [coll18_sox] GO /****** Object: StoredProcedure [dbo].[FormatData] Script Date: 07/30/2012 15:28:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTI FIER ON GO -- ============================================= -- Author: Margarita Uk -- Create date: 07/30/12 -- Description: formats data into fact/dimension format -- ============================================= ALTER PROCEDURE [dbo].[FormatData] -- Add the parameters for the stored procedure here AS BEGIN DECLARE @ProcessedData AS TABLE (ID uniqueidentifier,ReunionClass varchar(50)) DECLARE @myid uniqueidentifier SET @myid = NEWID() DECLARE @Reun varchar(50) DECLARE vend_cursor CURSOR FOR SELECT DISTINCT REUNION_CLASS FROM PERSON_LS OPEN vend_cursor IF EXISTS (SELECT * FROM ReportPrep.dbo.ReunionDim) BEGIN DELETE FROM ReportPrep.dbo.ReunionDim -- WHERE your condition END WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM vend_cursor into @Reun Insert into @ProcessedData(ID,ReunionClass)VALUES ((@myid),@Reun) INSERT INTO ReportPrep.dbo.ReunionDim (ID,ReunionClass) SELECT ID,ReunionClass FROM @ProcessedData END; close vend_cursor -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- insert fresh END
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 CREATE PROCEDURE FormatData @sourcTable sysname, @sourceColumn sysname, @targetTable sysname, @targetColumn sysname BEGIN DECLARE @SQL = N'INSERT dbo.' + @targetTable + ' (' + @targetColumn + ',ID) SELECT DISTINCT '+ @sourceColumn + ', NEWID() FROM ' + @sourceTable +'''; EXEC sp_executeSQL @SQL END 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.
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: CREATE PROCEDURE FormatData BEGIN INSERT ReportPrep.dbo.ReunionDim (ReunionClass,ID) SELECT DISTINCT REUNION_CLASS, NEWID() FROM PERSON_LS; END 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.
So then, use the MERGE statement as I said. That will let you determine if the class is already in place. You really don't need a cursor and you don't need to delete existing data to insert new data. You could also do a LEFT JOIN to the existing table and only move rows that are NULL as part of the JOIN (meaning they exist in table, but not the other). You've got options.
9 People are following this question.
