Solved

ms sql script to insert the current datetime in a record

Posted on 2014-03-31
10
336 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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