SQLTeam.com | Weblogs | Forums

Copy data from one table to another and append current year's date?

Greetings expert helpers.

I thought I could do this rather easily.

I have the following table design:

Table Name: Sermons:
|sermonID Int PK,
|SpeakerID Int,
|dateFilmed datetime,
|sermonTitle nvarchar(50),
|Scripture nvarchar(50),
|VideoURL nvarchar(50),
|SermonNotes nvarchar(MAX)

I was asked to copy the contents of this table called Sermons into another table and name it Sermons_19. They would like us to append the current year into this new table in such that the table would have in addition to the fieldnames above, a field name called year_created and would have an integer value of say 2021.

Even though this fieldname does not exist on the table that I am trying to copy from - Sermons, I thought I could add the the year_created fieldname on the new table.

The following is causing an error:

SELECT [SermonID],[SpeakerID]
  ,[dateFilmed]
  ,[sermonTitle]
  ,[Scripture]
  ,[VideoURL]
  ,[SermonNotes]
  ,year_created INTO Sermons_21 [sermonID]
  ,[SpeakerID]
  ,[dateFilmed]
  ,[sermonTitle]
  ,[Scripture]
  ,[VideoURL]
  ,[SermonNotes]
  ,YEAR(getdate())
  FROM [dbo].[Sermons]

Can this be done?

Many thanks in advance

Never mind, this worked:

SELECT [SermonID],[SpeakerID]
      ,[dateFilmed]
      ,[sermonTitle]
      ,[Scripture]
      ,[VideoURL]
      ,[SermonNotes]
	  ,year_created=2021 INTO Sermons_21
  FROM [dbo].[Sermons]

If there is a better way, please let me know.

Again, many thanks