Insert date modified column

Question

Post reply

Insert date modified column

ng_rai

SSC Enthusiast

Points: 189
More actions
September 26, 2019 at 6:47 pm

#3683747

Hi,
I want to insert date modified column in a table which getting created by some @declare table.
When I am adding date modified column after creating a table in database from @declare table by using Altertable syntax it just gives the latest time and date which i dont want.
I want to create a column while creating table through @declare table and @ declare table does not have datecreate/date modified column.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246723 More actions · Answer 1

Can you please try explaining this again, because I don't understand what the problem is.

Note that @declare table is not valid T-SQL syntax – what do you really mean?

Can you provide some sample code? It might make things clearer.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ng_rai SSC Enthusiast Points: 189 More actions · Answer 2

declare @abc varchar(50)

set @abc = 'Accesstest.dbo.[ Historical Data_' + (select cast(year(getdate()) - 2 as varchar(4))) + ']'

declare @query varchar(max)

set @query = '

if object_id(''AccessTest.dbo.ABC'') is not null drop table AccessTest.dbo.ABC

select

[Company Number]

,[Company Name]

,[Total Equity] as TotalEquity_ABC

,[Net Income] as NetIncome_ABC

Exec @Query

I want to add datecreated/datemodified in "AccessTest.dbo.ABC". Hope this is helpful.

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

Why all the dynamic sql? It is not only not needed but seems to have nothing at all to do with what you are describing you want to. If you want to add a new column to a table the right way is to use the ALTER TABLE statement, not drop the table.

Something like this.

alter table AccessTest.dbo.ABC
add DateCreate datetime

My concern though is that you may have tables with dates in the name. This is a terrible design approach as that kind of information should be a column in a single table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/