February 5, 2008 at 11:41 am
What is the best way to select into a table and append a current timestamp at the end?
February 5, 2008 at 11:51 am
Add a column to the table like this:
alter table dbo.Table
add InsertedOn datetime default(getdate())
Then it will automatically put the date and time (down to +/-3 milliseconds) in each row.
If you don't need that precision, use smalldatetime instead of datetime.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:07 pm
Hi,
I need to append the timestamp to the name of the table when running the select into
Right now I am getting conversion errors but have done the following:
declare @date timestamp
set @date = getdate()
exec ('SELECT * INTO table_Backup_'+@date+'FROM table' )
February 5, 2008 at 1:02 pm
Use:
declare @date datetime
set @date = getdate()
exec ('SELECT * INTO [table_Backup_'+cast(@date as varchar(100))+'] FROM table' )
First, you need to declare @date as datetime, not timestamp. (Look up timestamp in Books Online. It's misnamed and MicroSoft is recommending not using it any more, but it has nothing to do with dates and times.)
Second, you need to cast/convert it to varchar instead of datetime in the exec statement.
If you want to avoid those steps, declare @date as varchar in the first place, then set it to getdate. If you do that, you get the default format. If you declare it as datetime, then convert it later, you can set the format you want. (See "CAST and CONVERT" in Books Online, it will tell you the format choices you have.)
You also will need to add braces "[" and "]" around the table name, or the exec command will give you an error. Table names with spaces in them (date and time have spaces), or special characters (pretty much, punctuation other than "_"), need to have square-braces around them.
You can also use smalldatetime, instead of datetime, for the @date, which will record the minutes, but not the seconds and fractions of a second. If that works, use it.
(I know that's a bit much, but it all matters.)
Let me know if you have any further questions on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:14 pm
Hi,
Thanks allot for your help! The only thing is I am getting an incorrect syntax near cast when i execute this. I am looking into the other options you described but any idea why this statement is not working? Thanks again.
Dave
February 5, 2008 at 1:40 pm
Please copy and paste your code into the forum, and the error message you get.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:47 pm
This is the code I am running:
declare @date datetime
set @date = getdate()
exec ('SELECT * INTO [IDPSystemFolders_Backup_'+cast(@date as varchar(100))+'] FROM [IDPSystemFolders]' )
This is the error:
Line 4: Incorrect syntax near 'cast'.
Thanks!
February 5, 2008 at 2:01 pm
It's been so long since I used it, I forgot that exec doesn't like string functions. Try this:
declare @date datetime, @cmd varchar(1000)
set @date = getdate()
set @cmd = 'SELECT * INTO [IDPSystemFolders_Backup_'+cast(@date as varchar(100))+'] FROM [IDPSystemFolders]'
exec (@cmd)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 2:04 pm
If that does what you need, you might want to try replacing the exec command with sp_executeSQL. It works better, and can take input parameters and such. Books Online has an article on using it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 2:07 pm
Thank you very much!
February 5, 2008 at 2:11 pm
I'm assuming all of this is to create a logging function. You'll run it on a schedule, and thus have a sort of picture of the state of the table as of X time.
If so, there are better solutions to that kind of thing. If it's for auditing purposes, a log parser is better for that, and more legally defensible. If it's for reporting, there are ways to log data as it changes, so you can see change by change, instead of "some time between 4 and 5 PM, it was changed". If it's for rolling back changes (undoing them), a change-by-change logging solution is better for that too.
If there's a real need for creating a new table each time this is run, with a timestamp in the table name, just keep in mind that your database is probably going to grow like crazy because of that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2008 at 10:18 am
Hi I need this format TableName_YYYYMMDD
Anyone can help me.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply