Before we do anything we’re first going to need a copy of the table, including the source data.To do this we use select into and move everything into a temporary table: DROP TABLE dbo.[Resource Order Customers Orders]; CREATE TABLE [dbo].[Resource Order Customers Orders] ( [Order Id] IDENTITY(1, 1) NOT NULL , [Customer Id] INT NOT NULL , [Resource Id] INT NOT NULL , [Quantity] INT NULL , [Created] DATETIME NOT NULL , [Created By] VARCHAR(20) NOT NULL , [Modified] DATETIME NULL , [Modified By] VARCHAR(300) NULL , [Order Date] DATETIME NULL , [Approved Date] DATETIME NULL , [Processed Date] DATETIME NULL , [Rejected Date] DATETIME NULL ); We now need to re-insert our data from the temporary table into the production one.The first approach that may come to mind is to add an identity column to your table if the table does not already have an identity column.We will take a look at this approach as well as looking at how to do this with a simple UPDATE statement.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 17 ms. Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.In this step we are doing an UPDATE to the table and for each row that is updated we are updating the variable by 1 as well as the id column in the table.This can be seen here (SET @id = id = @id 1) where we are making the @id value and the id column equal to the current @id value 1.
Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below. Dwl Entry(User Name, Config Item, Component, Time Logged) VALUES(‘Bhanu’,’AFOTA’,’Code Change’,’3′) INSERT INTO dbo.
However, there is an easy way to accomplish this action. This table is vacant, with no records, as you can see in the following screenshot. You will notice that T-SQL is used by SQL Server to make this change. Dwl Entry ( Id INT IDENTITY(1,1), User Name VARCHAR(100), Config Item VARCHAR(100), Component VARCHAR(100), Time Logged DECIMAL(13,2) )INSERT INTO dbo.
After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. Dwl Entry(User Name, Config Item, Component, Time Logged) VALUES(‘Bubai’,’AFOTA’,’Documentation’,’3′) INSERT INTO dbo.
If you 're not a greek language reader, you can find english only categories and rss feeds for both posts and articles in my blog.
Just look for "Posts in English" and "Articles in English" at the lower left corner of this page.
This article contribution from one of my favorite SQL Expert Imran Mohammed.