Solved

ms sql script to insert the current datetime in a record

Posted on 2014-03-31
10
338 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Show RTF format in an SSRS report 3 37
Need sql in string 2 28
Can I skip a node in XML? 9 29
SQL Select Query help 1 34
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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