Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms sql script to insert the current datetime in a record

Posted on 2014-03-31
10
Medium Priority
?
346 Views
Last Modified: 2014-04-01
Need help to insert the current datetime in the table:
Error:
Msg 102, Level 15, State 1, Procedure Email_SCARLogCopy_Insert_Update, Line 25
Incorrect syntax near '@DateTime'

which is at line:

      INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld , @DateTime, IsInsert)




SCRIPT:



CREATE TRIGGER [Email_SCARLogCopy_Insert_Update] ON  [dbo].[SCARLogCopy]
   AFTER INSERT, UPDATE
AS
BEGIN
  set nocount on

  declare @SCAR_No varchar(38), @s_sub nvarchar(1000), @sqlstr nvarchar(MAX), @DateTime datetime
  set @DateTime = getdate()

      INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld , @DateTime, IsInsert)
                select I.SCAR_No, I.AreaRequiringCA, D.AreaRequiringCA, case when D.SCAR_No is null then 1 else 0 end
                  from Inserted I
                  left join Deleted D on D.SCAR_No = I.SCAR_No
                  
      while exists (select 1 from SCAREmailTempTable) begin
        select top 1 @SCAR_No = SCAR_No
      , @sqlstr = case when IsInsert = 1 then 'Area Requiring CA: ' + AreaRequiringCA else 'SCAR-' + SCAR_No + ' AreaRequiringCA has been changed FROM: ' + char(13) + char(13) + AreaRequiringCAOld + char(13) + char(13) + 'TO' + char(13) + char(13) + AreaRequiringCA end
      , @s_sub = case when IsInsert = 1 then 'New SCAR ' + SCAR_No+ ' was generated' else 'Updated SCAR ' + SCAR_No + ' Area Requiring CA' end
      from SCAREmailTempTable
   

--    delete from @Updates where @SCAR_No = SCAR_No
--    delete from SCAREmailTempTable where @SCAR_No = SCAR_No
 --   DROP TABLE SCAREmailTempTable
  end
END
GO
0
Comment
Question by:sxxgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39968108
You've got @DateTime as an insert field for your temp table. The column name won't start with '@', you need to add the correct column name to the insert statement, and move you @DateTime to the select which is generating the values for the insert.

I'd show you but I don't know what you've called the column in your temp table.

Also there is no requirement to use a variable, you can select getdate() directly.

Even easier you can set the default on that table column as getdate() and never have to update it directly.
0
 

Author Comment

by:sxxgupta
ID: 39968144
Column is called Date.

Thanks Dale.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39968201
Does that all make sense? Or do you need me to put it together?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:sxxgupta
ID: 39968228
Can you pls put it together.  I tried your suggestions and keep getting different errors.....................

Sorry.


Sanj
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39968234
Can you post the definition of "SCAREmailTempTable"
0
 

Author Comment

by:sxxgupta
ID: 39968994
Five fields:

(SCAR_No varchar(38),AreaRequiringCA nvarchar(max),AreaRequiringCAOld nvarchar(max), IsInsert bit, Date datetime)

"Even easier you can set the default on that table column as getdate() and never have to update it directly."......I like this approach...not sure though how to implement
0
 

Author Comment

by:sxxgupta
ID: 39969008
I have tried this:

CREATE TABLE SCAREmailTempTable(SCAR_No varchar(38),AreaRequiringCA nvarchar(max),AreaRequiringCAOld nvarchar(max), IsInsert bit, Date datetime default getdate())
   
INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld , IsInsert, getdate())


And get an error:
Msg 102, Level 15, State 1, Procedure Email_SCARLogCopy_Insert_Update, Line 24
Incorrect syntax near '('.
which is at the insert into statement
0
 

Author Comment

by:sxxgupta
ID: 39969011
tried this:

    INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld , IsInsert, TempDate)
                select I.SCAR_No, I.AreaRequiringCA, D.AreaRequiringCA, TempDate, case when D.SCAR_No is null then 1 else 0 end
                  from Inserted I
                  left join Deleted D on D.SCAR_No = I.SCAR_No

and faulting at the select statement:

Msg 207, Level 16, State 1, Procedure Email_SCARLogCopy_Insert_Update, Line 25
Invalid column name 'TempDate'.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 2000 total points
ID: 39970287
Once you have set the default for the [date] column you don't need to insert it. So just do this

INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld, IsInsert)

Open in new window


Even if you were to insert it, the insert statement only ever has columns names, never expressions so it would be

INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld, [Date], IsInsert)

Open in new window


And the full statement would be (you don't need to do this as well as the default)

INSERT INTO SCAREmailTempTable(SCAR_No, AreaRequiringCA, AreaRequiringCAOld, [Date], IsInsert)
                select I.SCAR_No, I.AreaRequiringCA, D.AreaRequiringCA, getdate(), case when D.SCAR_No is null then 1 else 0 end
                  from Inserted I
                  left join Deleted D on D.SCAR_No = I.SCAR_No

Open in new window

0
 

Author Closing Comment

by:sxxgupta
ID: 39970379
Thanks Dale.  I actually finally got it working very similar to your suggestion.  Thanks again.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question