February 2, 2017 at 12:50 pm
ALTER TABLE [dbo].[emp] ADD [ID] INT IDENTITY(0000001, 1)
SELECT IR.JournalSource,
RIGHT('0000000' + CAST(IR.[ID] AS VARCHAR(30)), 7)
INTO #IRTemp
FROM [dbo].[emp] IR
ALTER TABLE [dbo].[emp] DROP COLUMN ID
When i try to execute stored procedure its showing Invalid column ID.
What is the issue here, It is pretty much straight forward
February 2, 2017 at 1:27 pm
How about an alternative which does not stuff up your Emp table?
CREATE TABLE #IRTemp
(
Id INT IDENTITY(1, 1)
, JournalSource VARCHAR(30)
);
INSERT #IRTemp
(
JournalSource
)
SELECT IR.JournalSource
FROM dbo.emp IR;
SELECT
it.Id
, it.JournalSource
, Id2 = RIGHT('0000000' + CAST(it.Id AS VARCHAR(30)), 7)
FROM #IRTemp it;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 2, 2017 at 2:18 pm
mcfarlandparkway - Thursday, February 2, 2017 12:50 PMALTER TABLE [dbo].[emp] ADD [ID] INT IDENTITY(0000001, 1)
SELECT IR.JournalSource,
RIGHT('0000000' + CAST(IR.[ID] AS VARCHAR(30)), 7)
INTO #IRTemp
FROM [dbo].[emp] IRALTER TABLE [dbo].[emp] DROP COLUMN ID
When i try to execute stored procedure its showing Invalid column ID.
What is the issue here, It is pretty much straight forward
you are running all of that in 1 transaction. break it up into multiple transactions with GO statements and you should be good.
Although why are you adding and dropping the ID column? you could probably get by using row_number(), no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply