Insert Statement & Select Statement

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

General SQL Server Forums

New to SQL Server Programming

Insert Statement & Select Statement

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2012-01-17 : 10:31:38

Good Morning:

First: I created stored procedure, that collect the input information from the user. The stored procedure uses parameters to insert the information inside a Table:
----------------------------------------------------------------------

INSERT INTO dbo.SHP_Project(Project_Number, Employee,Client_Commit_date,Ship_Date, Location_Name,
Sales_Dollars,Actual_SVM_percentage,Impact_Criteria, Impact_Level, Comments)
values(@Project,@ProjectManag, @CommitDate ,@ShipDate, @Location, @Sales,
@PercentSVM,@ImpactCriteria,@CriteriaLevel,@Comments)
---------------------------------------------------------------------

Second: Inside that table is a Calculated field, that needs to be inserted, everytime someone enter a new project. Project_Number field is a Primary Key field. This field cann't be blank. The next statement does the calculation but I don't know how to write both statement together. So, when the user enter a new project, the calculation is done and insert it in the table at the same time.
----------------------------------------------------------------------
SELECT DATEDIFF( week,client_commit_date,ship_date) as WeeksLate
FROM SHP_Project
where Project_Number =@Project
END

---------------------------------------------------------------------
Thank you in advance for all your help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 10:34:46

I'm assuming you are using a cursor

Post that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-17 : 10:57:27

I'm confused by your statement:
"Inside that table is a Calculated field, that needs to be inserted, everytime someone enter a new project."

A calcualted COLUMN is not inserted, per se, as it is calculated from other COLUMNs in that table. Are you trying to take a calcualted column value and insert it into another table or are you trying to do something else?

Also, DDL, DML and Expected output can be very helpful when trying to illustrate your question. For how to do that, please see this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2012-01-17 : 11:22:04

Ok. sorry for the confussion.
1. I am gathering the input for each colum from the User using stored procedures & parameters.
2. Once I have all the information from each column. Then I have a calculation to perform between two of the columns entered.
3. I have the Calculation statement perform within a
SELECT DATEDIFF( week,client_commit_date,ship_date) as WeeksLate
FROM SHP_Project
where Project_Number =@Project
END
4. I need to be able to insert the calculation inside the specific column "WeeksLate" once the user finish entering all the other fields.
5. The calculation needs to happen at the end of the Insert Statement.
How can I do that?
Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-17 : 11:48:25

I'm still confused. Are you trying to insert that WeeksLate value to a different table? If not, why not just insert it with the other values?

INSERT INTO dbo.SHP_Project(Project_Number, Employee,Client_Commit_date,Ship_Date, Location_Name,
Sales_Dollars,Actual_SVM_percentage,Impact_Criteria, Impact_Level, Comments, WeeksLate)
values(@Project,@ProjectManag, @CommitDate ,@ShipDate, @Location, @Sales,
@PercentSVM,@ImpactCriteria,@CriteriaLevel,@Comments, DATEDIFF(week, @CommitDate, @ShipDate))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-17 : 11:49:44

Moreover, why not make Weekslate a Calulated Column and then you don't even have to worry about it begin inserted.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2012-01-17 : 11:59:56

Lamprey:

I am trying to insert this value in the same table SHP_Project. How do I make Weekslate a Calculated Column? thanks for the help.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2012-01-17 : 12:04:09

Thanks for the help Lamprey...you were right I made into a calculated field.