Insert with Select - 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)

Insert with Select

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-06-13 : 10:52:06

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

52326 Posts

Posted - 2012-06-13 : 10:58:31

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/

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-06-13 : 10:59:27

brilliant thank you!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 11:04:28

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.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 11:04:56

tooo late...........

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-06-13 : 11:19:06

its never too late Charlie - thanks for your feedback

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources