I need to copy records from a table, back into the same table changing the value of on field only.

Question

Post reply

I need to copy records from a table, back into the same table changing the value of on field only.

randyetheridge

SSCommitted

Points: 1703
More actions
May 23, 2016 at 12:02 pm

#307986

I used this code, it does not like the "set" statement. how should the code be to copy all the budget2016 records into the same table, but with budget2013 as the scenario id. I am doing some testing of the front end and wanted the same data in Budget2013 as I had in budget2016.
Insert INTO (
set [ScenarioID]='Budget2013'
,[CostCenterID]
,[CostCenterName]
,[FunctionID]
,[FunctionName])
SELECT [ScenarioID]
,[CostCenterID]
,[CostCenterName]
,[FunctionID]
,[FunctionName]
FROM [US_DWH_NEW].[dbo].[BudgetMasterCostCenterHierarchy]
where ScenarioID='budget2016'

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's just syntax.

you want to select modified data, and insert is still just an insert. i saw what you were tryingto do, though.

below is a better example

Insert INTO (

[ScenarioID]

,[CostCenterID]

,[CostCenterName]

,[FunctionID]

,[FunctionName])

SELECT 'Budget2013' AS [ScenarioID]

,[CostCenterID]

,[CostCenterName]

,[FunctionID]

,[FunctionName]

FROM [US_DWH_NEW].[dbo].[BudgetMasterCostCenterHierarchy]

where ScenarioID='budget2016'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

ok, got it, you are correct I was focusing on the INSERT side of the code, not the select side. I see my mistake now. Thanks I have about six tables that I need to do this for, and this will speed it up considerably.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lowell (5/23/2016)
it's just syntax.
you want to select modified data, and insert is still just an insert. i saw what you were tryingto do, though.
below is a better example
Insert INTO (
[ScenarioID]
,[CostCenterID]
,[CostCenterName]
,[FunctionID]
,[FunctionName])
SELECT 'Budget2013' AS [ScenarioID]
,[CostCenterID]
,[CostCenterName]
,[FunctionID]
,[FunctionName]
FROM [US_DWH_NEW].[dbo].[BudgetMasterCostCenterHierarchy]
where ScenarioID='budget2016'

Table name is missing from the INSERT.