Need help writing SQL query to insert single row into table using current date information

Transact-SQL
1

Hi everyone

I have a SQL Sever 2014 database that is used for our Helpdesk system.
I am wanting to write a SQL Query that I can schedule to run every morning at 6:00am that will insert a single row into a table.
Bascially I have a reoccuring Helpdesk call that needs to be logged every day and I'm wanting to automate this task.

This probably sounds simple enough but I need help with the syntax when it comes to getting the current date/time when the query runs and inserting this information into one of the fields.

Let me explain in greater detail.

The table where the information is to inserted into is called HELPDESK
The HELPDESK table has the following fields that need filling in

[CID] varchar Client ID field. This will always be set to ADG144
[TID] [int] IDENTITY(1,1) Ticket ID. This is the Helpdesk Call # that is assigned to the call. TID is autoassigned.
[OPENTIME] [datetime] Date and Time when the Helpdesk call was logged. Need the script to enter the current date/time when script runs
[ASSIGNEDTO] varchar Engineer who call is assigned to. Always set to JOHN
[SUBJECT] varchar Helpdesk Call Subject. Needs to say Onsite Support - xxxx where xxx is the current day and date eg Onsite Support - Tuesday 1/3/16
[STATUS] varchar Status of call - This will always be set to OPEN

So as you can see there are two fields OPENTIME and SUBJECT where the script must extract date information and enter it into the field

Hopefuly this explains things enough for you to follow.

Thanks in advance for any help provided.
Regards
Steve

2

Hi @hnssnv,

Please try this one,

CREATE TABLE HELPDESK (
CID varchar(255) NOT NULL DEFAULT ('ADG144'),
TID int IDENTITY (1, 1) NOT NULL,
OPENTIME datetime NOT NULL DEFAULT (GETDATE()),
ASSIGNEDTO varchar(255) NOT NULL DEFAULT ('JOHN'),
SUBJECT varchar NOT NULL,
STATUS varchar NOT NULL DEFAULT ('OPEN')
)

INSERT INTO HELPDESK (SUBJECT)
VALUES ('Onsite Support - ' + DATENAME(DW, GETDATE()) + ' ' + CAST(DAY(SYSDATETIME()) AS varchar(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS varchar(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS varchar(4)), 2))

Thanks.

1 Like
3

Thanks Jai.
I will test this out but I don't want to create a table with default values. The table already exists and contains other helpdesk calls. This insert statement is just for 1 schedule call per day and whilst it has default values other calls that are manually logged dont use the same default values.
Thanks for your time
Steve

4

you will need to Schedule a Job

https://msdn.microsoft.com/en-us/library/ms191439.aspx

5

:slightly_smiling:

Schedule a JOB (using SQL Agent), make sure you select the appropriate database and execute this SQL statement

INSERT INTO  HELPDESK
(
    CID,
--    TID,     -- Identity
    OPENTIME, ASSIGNEDTO, SUBJECT, STATUS
)
SELECT	[CID] = 'ADG144',
	[OPENTIME] = GetDate(),
	[ASSIGNEDTO] = 'JOHN',
	[SUBJECT] = 'Onsite Support - '
	+ DATENAME(weekday, GetDate()) + ' '
	+ CONVERT(varchar(8), GetDate(), 3) -- NOTE: d/m/y format WITH leading zeros
	[STATUS] = 'OPEN'
1 Like
6

Thanks Kristen
This worked a treat.

Steve