Creating tables from SP's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating tables from SP's

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-07-31 : 11:23:45

I am sure that there is an easy solution which I can't find, but here is my problem

I have created a stored procedure (sales) , which returns the information that I need but it does take a long time, and I want the results to be created into a table in sql.

Below is the stored procedure, if someone could show me how to get the results to be created into a table, it would be great.

Thanks

I am sure that there is an easy solution which I can't find, but here is my problem

I have created a stored procedure (sales) , which returns the information that I need but it does take a long time, and I want the results to be created into a table in sql.

Below is the stored procedure, if someone could show me how to get the results to be created into a table, it would be great.

Thanks

CREATE PROCEDURE [dbo].[sales] AS
SELECT TOP 100 PERCENT dbo.EMPFIL.emp_store AS Store, dbo.Rep_Calendar.[Year], dbo.Rep_Calendar.Week, dbo.DYDFIL.dyd_emp_code AS Employee,
dbo.EMPFIL.emp_name, SUM(dbo.DYDFIL.dyd_sold_price) AS Sold, SUM(dbo.DYDFIL.dyd_qty) AS Qty,
COUNT(DISTINCT dbo.DYDFIL.dyd_alt7_seq_no) AS [Transaction]
FROM dbo.DYDFIL INNER JOIN
dbo.Rep_Calendar ON dbo.DYDFIL.dyd_date = dbo.Rep_Calendar.CivilDate INNER JOIN
dbo.EMPFIL ON dbo.DYDFIL.dyd_emp_code = dbo.EMPFIL.emp_code
GROUP BY dbo.EMPFIL.emp_store, dbo.DYDFIL.dyd_emp_code, dbo.Rep_Calendar.[Year], dbo.Rep_Calendar.Week, dbo.EMPFIL.emp_name
HAVING (dbo.Rep_Calendar.[Year] >= 2002)
ORDER BY dbo.EMPFIL.emp_store, dbo.Rep_Calendar.[Year] DESC, dbo.Rep_Calendar.Week DESC, dbo.DYDFIL.dyd_emp_code
GO

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-31 : 11:28:22

[code]
CREATE PROCEDURE [dbo].[sales] AS
SELECT TOP 100 PERCENT dbo.EMPFIL.emp_store AS Store, dbo.Rep_Calendar.[Year], dbo.Rep_Calendar.Week, dbo.DYDFIL.dyd_emp_code AS Employee,
dbo.EMPFIL.emp_name, SUM(dbo.DYDFIL.dyd_sold_price) AS Sold, SUM(dbo.DYDFIL.dyd_qty) AS Qty,
COUNT(DISTINCT dbo.DYDFIL.dyd_alt7_seq_no) AS [Transaction] INTO [..tableName..]
FROM dbo.DYDFIL INNER JOIN
dbo.Rep_Calendar ON dbo.DYDFIL.dyd_date = dbo.Rep_Calendar.CivilDate INNER JOIN
dbo.EMPFIL ON dbo.DYDFIL.dyd_emp_code = dbo.EMPFIL.emp_code
GROUP BY dbo.EMPFIL.emp_store, dbo.DYDFIL.dyd_emp_code, dbo.Rep_Calendar.[Year], dbo.Rep_Calendar.Week, dbo.EMPFIL.emp_name
HAVING (dbo.Rep_Calendar.[Year] >= 2002)
ORDER BY dbo.EMPFIL.emp_store, dbo.Rep_Calendar.[Year] DESC, dbo.Rep_Calendar.Week DESC, dbo.DYDFIL.dyd_emp_code
GO
[/code]

Note that the tableName does not have to be created prior to running the Sp, it will create the table based on the information in your SELECT cluase. So just add this bit in red to your code and you should have a new table containing the results of your query.

Search for SELECT ......INTO for more info. Good luck!

----------------
Shadow to Light

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-07-31 : 11:41:24

Worked great

Thanks a lot

Mufasa

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 12:30:29

Yeah but you'll have to manage it. If it isn't dropped and you rerun you'll because the object already exists.

You can DROP When you're done (but if another process calls the sproc you're toast)
Use a local temp table...it'ss go away when the process is complete

Or create a permanent table, use the spid as a column and insert the data with the spid..

MOO

But why do you want to do this? Why not create a view?

Brett

8-)

SELECT POST=NewId()

chadmat
The Chadinator

1974 Posts

Posted - 2003-07-31 : 13:09:02

Or a function that rerturns a table variable.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-01 : 05:58:11

quote:

But why do you want to do this? Why not create a view?

Seems like the better choice in this instance.

----------------
Shadow to Light

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-09-03 : 11:20:51

The reason I need to create the table, is that the DYDFIL holds more than 10 million records. Just creating this table is very server intensive, and I run this SPROC as a job, so when I get in in the moring it is already created.

I also added Drop Table to my SPROC

Wouldn't a view need to be run each time you need to see the data.
The information created from the SPROC gets accessed frequently as I create reports based on the information.

I appreciate your comments as I am still new to SQL

Mufasa

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 13:31:57

Hey how's it going?...millions of rows ...ahh

How about something like this...



-- First create your table outside of the nightly script

CREATE TABLE DENORMALIZED_DATA (....
GO

-- Then Nightly Truncate...a non logged op

TRUNCATE TABLE DENORMALIZED_DATA
GO

-- Then try this and see how it performs...in any case lose the order by..

SELECT    Store
	, [Year]
	, [Week]
	, Employee
	, emp_name
	, SUM(d.dyd_sold_price AS Sold
	, SUM(d.dyd_qty) AS Qty
	, COUNT(DISTINCT d.dyd_alt7_seq_no) AS [Transaction]
INTO DENORMALIZED_DATA 
FROM (
	    SELECT	  e.emp_store AS Store
			, c.[Year]
			, c.[Week]
			, d.dyd_emp_code AS Employee
			, e.emp_name
			, d.dyd_sold_price
			, d.dyd_qty
			, d.dyd_alt7_seq_no
	      FROM 	  dbo.DYDFIL d 
	INNER JOIN 	  dbo.Rep_Calendar c 
		ON 	  d.dyd_date = c.CivilDate 
	INNER JOIN 	  dbo.EMPFIL e 
		ON 	  d.dyd_emp_code = e.emp_code
	     WHERE 	  c.[Year] >= 2002
) AS XXX
GROUP BY  Store
	, [Year]
	, [Week]
	, Employee
	, emp_name

To restate...take out the order by at the very least!

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources