CODEWITHSUNDEEP
.netsqlsql-server-2005t-sql
sgibbons
sgibbons

Reputation: 3630

SELECT INTO with an additional column

I am attempting to insert a copy of a row from one table into another table with the same schema, with the addition of one column (a timestamp) to provide a "history" of the first table in MS Sql Server 2005.

So, my query, without the additional column would be:

"SELECT INTO [WebsiteHistory] FROM [Website]"

I want to populate the timestamp column as well, but am not sure how to best do this. I'd like to do something like:

"SELECT Website.*, '" + DateTime.Now.ToShortDateString() + "' INTO [WebsiteHistory] FROM [Website]"

But that shouldn't work, especially if the timestamp column is not the last one. Is there any way to do this?

Upvotes: 2

Views: 3802

Answers (3)

Oddthinking
Oddthinking

Reputation: 25312

Look at the accepted answer for this question: Dynamically look up column names for a table while in an sql query

It fetches the names of all the fields in the table to create a query customized to that table. You should be able to simply adapt the exact same technique to generate the right fields names here.

Upvotes: 0

Simon Keep
Simon Keep

Reputation: 10002

Can't you set a default constraint on the column that would automatically populate the timestamp column when a row is inserted to the table?

Upvotes: 2

Tomalak
Tomalak

Reputation: 338248

Be warned. This works, but it is neither nice nor recommendable:

INSERT
  WebsiteHistory
SELECT
  *,
  GETDATE()
FROM
  Website
WHERE
  Id = @WebsiteId

This assumes WebsiteHistory has the same structure as Website (you said it has), plus there is one additional DATETIME field.

Better is this, because it is much more fail-safe (at the expense of being more verbose):

INSERT
  WebsiteHistory
  (
  Id,
  Field1,
  Field2,
  Field3,
  Field4,
  ModifiedDate
  )
SELECT
  Id,
  Field1,
  Field2,
  Field3,
  Field4,
  GETDATE()
FROM
  Website
WHERE
  Id = @WebsiteId

Upvotes: 14

Related Questions