SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert with Select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 06/13/2012 :  10:52:06  Show Profile  Reply with Quote
hi I'm writing a SP that will insert records into a Table but I was wondering can I mix the insert up with a select for example:

CREATE PROCEDURE [dbo].[insApplianceDetailsLog] 
@ApplianceName			varchar(50),
@ApplianceRatedPower	varchar(4),
@TotalStorageCapacity	varchar(5),
@RemainingStorageCapacity varchar(3),
@CoreTemperature		varchar(3),
@RoomTemperature		varchar(3),
@ChargeStatus			bit,
@ComfortStatus			bit,
@MotorStatus			bit


INSERT INTO [Quantum_Interface].[dbo].[tbl_Appliance_Details_Log]
           ([DateEntered]           
           ,[ApplianceName]
           ,[ApplianceRatedPower]
           ,[TotalStorageCapacity]
           ,[RemainingStorageCapacity]
           ,[CoreTemperature]
           ,[RoomTemperature]
           ,[ChargeStatus]
           ,[ComfortStatus]
           ,[MotorStatus]
           ,[ApplianceID]
           ,[Password]
           ,[CHG1StartTime]
           ,[CHG1EndTime]
           ,[CHG1Temperature]
           ,[CHG2StartTime]
           ,[CHG2EndTime]
           ,[CHG2Temperature]
           ,[CMF1StartTime]
           ,[CMF1EndTime]
           ,[CMF1Temperature]
           ,[CMF2StartTime]
           ,[CMF2EndTime]
           ,[CMF2Temperature])
     VALUES
           (getDate()          
           ,@ApplianceName
           ,@ApplianceRatedPower
           ,@TotalStorageCapacity
           ,@RemainingStorageCapacity
           ,@CoreTemperature
           ,@RoomTemperature
           ,@ChargeStatus
           ,@ComfortStatus
           ,@MotorStatus
           
           SELECT 
		ApplianceID
		,Password
		,CHG1StartTime
		,CHG1EndTime
		,CHG1Temperature
		,CHG2StartTime
		,CHG2EndTime
		,CHG2Temperature
		,CMF1StartTime
		,CMF1EndTime
		,CMF1Temperature
		,CMF2StartTime
		,CMF2EndTime
		,CMF2Temperature
		FROM dbo.tbl_Appliance_Configuration_Settings
		WHERE ApplianceName = @ApplianceName			
			)
GO

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 06/13/2012 :  10:58:31  Show Profile  Reply with Quote
you can and it should be like


CREATE PROCEDURE [dbo].[insApplianceDetailsLog] 
@ApplianceName			varchar(50),
@ApplianceRatedPower	varchar(4),
@TotalStorageCapacity	varchar(5),
@RemainingStorageCapacity varchar(3),
@CoreTemperature		varchar(3),
@RoomTemperature		varchar(3),
@ChargeStatus			bit,
@ComfortStatus			bit,
@MotorStatus			bit


INSERT INTO [Quantum_Interface].[dbo].[tbl_Appliance_Details_Log]
           ([DateEntered]           
           ,[ApplianceName]
           ,[ApplianceRatedPower]
           ,[TotalStorageCapacity]
           ,[RemainingStorageCapacity]
           ,[CoreTemperature]
           ,[RoomTemperature]
           ,[ChargeStatus]
           ,[ComfortStatus]
           ,[MotorStatus]
           ,[ApplianceID]
           ,[Password]
           ,[CHG1StartTime]
           ,[CHG1EndTime]
           ,[CHG1Temperature]
           ,[CHG2StartTime]
           ,[CHG2EndTime]
           ,[CHG2Temperature]
           ,[CMF1StartTime]
           ,[CMF1EndTime]
           ,[CMF1Temperature]
           ,[CMF2StartTime]
           ,[CMF2EndTime]
           ,[CMF2Temperature])
    SELECT getDate()          
           ,@ApplianceName
           ,@ApplianceRatedPower
           ,@TotalStorageCapacity
           ,@RemainingStorageCapacity
           ,@CoreTemperature
           ,@RoomTemperature
           ,@ChargeStatus
           ,@ComfortStatus
           ,@MotorStatus           
           ,ApplianceID
	   ,Password
	   ,CHG1StartTime
	   ,CHG1EndTime
	   ,CHG1Temperature
	   ,CHG2StartTime
	   ,CHG2EndTime
	   ,CHG2Temperature
	   ,CMF1StartTime
	   ,CMF1EndTime
	   ,CMF1Temperature
	   ,CMF2StartTime
	   ,CMF2EndTime
	   ,CMF2Temperature
	FROM dbo.tbl_Appliance_Configuration_Settings
	WHERE ApplianceName = @ApplianceName			
			
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 06/13/2012 :  10:59:27  Show Profile  Reply with Quote
brilliant thank you!!
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3442 Posts
Posted - 06/13/2012 :  11:04:28  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I think you just a bit confused. Varibles can be included in the SELECT list.

Something like

INSERT INTO [Quantum_Interface].[dbo].[tbl_Appliance_Details_Log]
           ([DateEntered]           
           ,[ApplianceName]
           ,[ApplianceRatedPower]
           ,[TotalStorageCapacity]
           ,[RemainingStorageCapacity]
           ,[CoreTemperature]
           ,[RoomTemperature]
           ,[ChargeStatus]
           ,[ComfortStatus]
           ,[MotorStatus]
           ,[ApplianceID]
           ,[Password]
           ,[CHG1StartTime]
           ,[CHG1EndTime]
           ,[CHG1Temperature]
           ,[CHG2StartTime]
           ,[CHG2EndTime]
           ,[CHG2Temperature]
           ,[CMF1StartTime]
           ,[CMF1EndTime]
           ,[CMF1Temperature]
           ,[CMF2StartTime]
           ,[CMF2EndTime]
           ,[CMF2Temperature])
           
SELECT
	getDate()          
	, @ApplianceName
	, @ApplianceRatedPower
	, @TotalStorageCapacity
	, @RemainingStorageCapacity
	, @CoreTemperature
	, @RoomTemperature
	, @ChargeStatus
	, @ComfortStatus
	, @MotorStatus
	, ApplianceID
	, Password
	, CHG1StartTime
	, CHG1EndTime
	, CHG1Temperature
	, CHG2StartTime
	, CHG2EndTime
	, CHG2Temperature
	, CMF1StartTime
	, CMF1EndTime
	, CMF1Temperature
	, CMF2StartTime
	, CMF2EndTime
	, CMF2Temperature
FROM dbo.tbl_Appliance_Configuration_Settings
WHERE ApplianceName = @ApplianceName



Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3442 Posts
Posted - 06/13/2012 :  11:04:56  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
tooo late...........

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Go to Top of Page

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 06/13/2012 :  11:19:06  Show Profile  Reply with Quote
its never too late Charlie - thanks for your feedback
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.78 seconds. Powered By: Snitz Forums 2000