stored procedure to take data from query

Question

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

Answer 1

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.

Answer 2

0

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.

answered Jul 31 '12 at 10:32 AM

Grant Fritchey ♦♦
62.1k ● 12 ● 20 ● 66

I did this and it created 10 new ids for every reunion class

Jul 31 '12 at 02:16 PM muk

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.

Jul 31 '12 at 02:21 PM Grant Fritchey ♦♦

(comments are locked)

stored procedure to take data from query

Follow this question

Related Questions