CODEWITHSUNDEEP
sqlsql-servertimestampinsert-into
Luka
Luka

Reputation: 189

Insert into SQL Server timestamp

I have table1 and table2 and I am inserting data into table3 based on rows from tables 1 + 2. I need get evidence in to table 3 when was data insert.

Table1:

Column1
a
b
d
e
g
h
i

Table2:

Column1
a
b
c
d
e
f

Code:

INSERT INTO [dbo].[table3](column1)
    SELECT column1
    FROM [dbo].[table2]
    WHERE NOT EXISTS (SELECT
                      FROM [dbo].[table1]
                      WHERE column1 = column1);

Result:

Table3:

Column1  Column2
c
f

I need get in to exist table and exist column (varchar(50) or timestamp) any format of time which will show me time when was insert values in to table using my current script.

Example:

Table3:

Column1 Column2
c       4/4/2017 
f       4/4/2017

Few days later should table look like this :

Table3:

Column1 Column2
c       4/4/2017 
f       4/4/2017
x       5/4/2017  
y       6/4/2017
z       10/4/2019

Thanks for upgrade my script for :

Something like :

INSERT INTO table3 (Column2) 
VALUES (CURRENT_TIMESTAMP);

Upvotes: 0

Views: 19733

Answers (2)

user7715598
user7715598

Reputation:

Add some Audit columns for the table like below

[StatusCode] [varchar](1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_ConstraintName_CreatedDate]  DEFAULT (getdate()),
[LastModifiedByUserId] [int] NULL ,
[LastModifiedDate] [datetime] NULL,

Upvotes: 1

LONG
LONG

Reputation: 4610

Just ADD one more column into table3 by ALTER TABLE TABLE3 ADD COLUMN updated Datetime2 DEFAULT GETDATE()

And just do the inserts as what you did, every time you insert a new row into table3, the last field updated will be populated with the current date and time.

Upvotes: 3

Related Questions