How to retrieve upload/current time in each batch file uploaded in sql server

Question

I want to do a batch csv file upload process in cake.php and save data in sql server.I successful uploaded the csv file and get the current time. Each batch of file contain 7 row of data. I've facing a problem when each batch of file saved into the table named "RAS_Off_Upload", the Up_Time column (getdate from current upload process) will renew/replace all the previous batch row of Up_Time into same current time.So, i can't recognize each batch was uploaded on what time because each batch already become same time.

database column:

   [No]
  ,[RAS_Code]
  ,[Value]
  ,[Remark]
  ,[SOF]
  ,[Created_by]
  ,[CLN_No]
  ,[Lot_No]
  ,[Prod]
  ,[Date]
  ,[Time]
  ,[id]
  ,[Line]
  ,[Up_Time]
  ,[BatchLoadID]

i've create the SP

CREATE PROCEDURE [dbo].[test]

@Up_Time varchar(50)
AS
SET NOCOUNT ON;


UPDATE dbo.RAS_Off_Upload
SET dbo.RAS_Off_Upload.Up_Time = GETDATE()

thanks in advance! appreciate all the comment.

How about saving in `Up_Time` the time the record was created? — Radu Gheorghiu, Jun 18 '14 at 07:37
the Up_Time format will shown like this -> Jun 18 2014 2:41PM. That means during upload file do not have this column Up_Time. After uploaded to database i created another column named Up_Time to retrieve current upload time using sql query -> UPDATE dbo.RAS_Off_Upload SET dbo.RAS_Off_Upload.Up_Time = GETDATE() — user3745274, Jun 18 '14 at 07:44

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

You can skip running the stored procedure and just change the table definition and store by default in Up_Time the current system date for when the record was created (the import of the .csv batch).

You can do this with the code below. This will change your table's definition to populate Up_Time column with system date, if no value is specified for this column in your INSERT query.

ALTER TABLE dbo.RAS_Off_Upload
ADD CONSTRAINT col_UpTime
DEFAULT GETDATE() FOR Up_Time

So all you'd have to do when inserting data into your table is to insert in all other columns except Up_Time which will be automatically populated by default with the system date when the record was created.

Update:

In order to drop an already existing constraint on your table, use the code below:

DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command NVARCHAR(1000)

SET @table_name = 'dbo.RAS_Off_Upload'
SET @col_name = 'Up_Time'

SELECT @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.NAME
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
WHERE t.NAME = @table_name
    AND c.NAME = @col_name

--print @Command
EXECUTE (@Command)

After executing this code snippet just run the first query, right before I updated my answer.

edited May 23 '17 at 10:25

Community

1
1

answered Jun 18 '14 at 07:46

Radu Gheorghiu

20,049
16
72
107

thank you for the comment and explanation! I'm not sure why it will occur error:Cannot find the object "dbo.RAS_Off_Upload" because it does not exist or you do not have permissions.I'm sorry i'm new to sql server. – user3745274 Jun 18 '14 at 08:02
Try running the query without `dbo.RAS_Off_Upload`, just `RAS_Off_Upload` – Radu Gheorghiu Jun 18 '14 at 08:03
Then, is it possible that you do not have permission? Or does your user have DBO (admin) rights? – Radu Gheorghiu Jun 18 '14 at 08:08
@user3745274 If you manage to run the query (not the original request of this post) and if the solution has worked for you please do not forget to mark the answer as correct so others with similar issues will know what to do. – Radu Gheorghiu Jun 18 '14 at 08:16
Ya, user have DBO (admin). If i try on windows authentication it will show error: Msg 1781, Level 16, State 1, Line 1 Column already has a DEFAULT bound to it. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. Is it because i call upload function using cake php framework? – user3745274 Jun 18 '14 at 08:17
Is it possible that the column has already a DEFAULT set to NULL? Or another date? – Radu Gheorghiu Jun 18 '14 at 08:20
@user3745274 In case there already is a DEFAULT set to the column, you will have to drop that constraint and add a new one using the query in my answer. I will reply with the query to drop the constraint. – Radu Gheorghiu Jun 18 '14 at 08:27
You are right! It is because of the NULL.But after i execute second times it bounce back the error:Column already has a DEFAULT bound to it.Could not create constraint. See previous errors. – user3745274 Jun 18 '14 at 08:33
@user3745274 See my updated answer. You have the queries you need to run to drop the NULL DEFAULT constraint. – Radu Gheorghiu Jun 18 '14 at 08:35
It works perfectly! Thank you for helping! So, how to save this queries to run it automatically when each upload file? – user3745274 Jun 18 '14 at 08:44
@user3745274 You don't need to drop the constraint every time. If you dropped the initial constraint and ran my first query, which will add the `GETDATE()` DEFAULT, then you can just continue inserting data into `RAS_Off_Upload` table, without specifying the `Up_Time` column in the `INSERT`. – Radu Gheorghiu Jun 18 '14 at 08:47
Actually i just can run the drop constraint, then the first query still have error but i can see the date uploaded in database table. – user3745274 Jun 18 '14 at 08:57

score 0 · Answer 2 · answered Jun 18 '14 at 07:49

0

First of all, you are missing a WHERE clause in your sproc. Therefore, it always updates all records in dbo.RAS_Off_Upload.
If you really want to keep using the sproc, you need to add something like WHERE RAS_Off_Upload.No = <the No of the record to be updated>

Much better though, is to change the design of your table.
If you add a default value of getdate() or sysdatetimeoffset() to column Up_Time, it will automatically add the current datetime once you insert a record. Then you don't need a sproc at all.

answered Jun 18 '14 at 07:49

CoolWilly

416
2
5

Thank you for the comment! Can you give example for the change design of table? Thank you! – user3745274 Jun 18 '14 at 08:11
Two options: You can either use the query from the other answer (by Radu Gheorghiu), or (in SSMS) right click on the table, choose design, select the column Up_Time and add `getdate()` at the 'default value and binding' option. – CoolWilly Jun 18 '14 at 08:14
As already pointed out in the other answer, you need sufficient rights for these options. – CoolWilly Jun 18 '14 at 08:16
Thank you CoolWilly! I never know the table design can change. Thank you for helping. – user3745274 Jun 18 '14 at 08:44

How to retrieve upload/current time in each batch file uploaded in sql server

2 Answers2