Stored Procedure fill table - 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 2005 Forums

Transact-SQL (2005)

Stored Procedure fill table

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-04-27 : 15:15:43

Hi, I need to make a stored procedure that makes a table, and then insert data from existing tables in the just made table.

I figured out how to make the Stored procedure, and the table,
but how and where do i need to put the code to insert the data from existing tables
like @column1 = select sales from prices.

This is what i got now :

if exists
(select name from sysobjects
where name = 'SP_Price' and xtype = 'p')
drop procedure SP_Price
go
create procedure SP_Price
@TotalOld nvarchar (20)
@TotalNew nvarchar (20)
@New Old nvarchar (20)
AS
if exists (select * from sysobjects where id = object_id(N'[dbo].[salescheck]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[salescheck]

CREATE TABLE [dbo].[salescheck] (
           [id] [int] IDENTITY (1, 1) NOT NULL ,
           [Ori] [smallmoney]  NULL ,
           [New] [smallmoney] NULL ,
           [difference] [smallmoney] NULL

) ON [PRIMARY]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 15:27:59

Just to start with:

if exists
(select name from sysobjects
where name = 'SP_Price' and xtype = 'p')
drop procedure SP_Price
go
create procedure SP_Price (
@TotalOld [smallmoney] ,
@TotalNew [smallmoney] ,
@NewOld [smallmoney] )
AS
Begin

if exists (select * from sysobjects where id = object_id(N'[dbo].[salescheck]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[salescheck]

CREATE TABLE [dbo].[salescheck] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Ori] [smallmoney] NULL ,
[New] [smallmoney] NULL ,
[difference] [smallmoney] NULL

) ON [PRIMARY]

--
--
--Insert into [salescheck]
--Select <columns> from <sourceTable > Where <condition>

--Example:
Insert into SalesCheck ([ori],[New],[difference])
Select @TotalOld, @TotalNew, @NewOld

End

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 15:29:04

after crate table you can insert.


CREATE TABLE....

INSERT INTO [dbo].[salescheck](Ori, New, difference)
SELECT @TotalOld, @TotalNew, @NewOld

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2010-04-27 : 15:34:31

Thanks for the quick respons, but how to put the data in @totalOld?
is it like this

INSERT INTO [dbo].[salescheck](Ori, New, difference)
SELECT column1, column2, column3 from A [Table]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:53:03

for directly inserting from table to another you can use above. for getting value onto a variable you can use like


SELECT @TotalOld=column1, @TotalNew=column2, @NewOld =column3  from A [Table]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources