Solved

ms sql script to insert the current datetime in a record

Posted on 2014-03-31
10
327 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
Comment Utility
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
Comment Utility
Column is called Date.

Thanks Dale.
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
Does that all make sense? Or do you need me to put it together?
0
 

Author Comment

by:sxxgupta
Comment Utility
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
Comment Utility
Can you post the definition of "SCAREmailTempTable"
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:sxxgupta
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Dale.  I actually finally got it working very similar to your suggestion.  Thanks again.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now