Solved

ms sql script to insert the current datetime in a record

Posted on 2014-03-31
10
339 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
Sorting a SQL script 5 41
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
What does "Between" mean? 6 48
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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