x

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
more ▼

asked Jul 30 '12 at 08:45 PM in Default

muk gravatar image

muk
400 24 32 34

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Jul 31 '12 at 02:26 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

@Hakan, thank you this is exactly what I am looking for. I am actually doing just the same thing, our company needs a huge reporting solution and the best way I could find is to also put the metadata in a staging area.
Jul 31 '12 at 02:29 PM muk
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 31 '12 at 10:32 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x562
x402
x362

asked: Jul 30 '12 at 08:45 PM

Seen: 1246 times

Last Updated: Jul 31 '12 at 02:29 PM