Skip to main content

Posts

Showing posts with the label SQL Server

Identity Column in SQL Table

Identity Column in the SQL Table: How it works in SQL table? How Many Identity column can be created on the SQL table? How to insert user defined value in the Identity column? What will be the next value for the Identity column after the user defined value insertion in the table? Solution: Drop table #temp ; --Drop the temp table if it is already present Create table #temp --Create the temp table with identity column ( A int identity ( 1 , 1 ), --identity column --B int Identity(10,10), -- we can not create two identity column on a able C varchar ( 10 ) ) --insert values into table insert into #temp ( c ) values ( 'a' ) insert into #temp ( c ) values ( 'b' ) insert into #temp ( c ) values ( 'c' ) Select * from #temp --set identity insert on ont the table so that we can insert a user defined value in the identity column SET IDENTITY_INSERT #temp on insert into #temp ( A , C ) values ( 5 , 'd' ) --set ide...

How to update values in a column based on the values from other tables

How to update column values in table based on values from two tables: Some time we need to update values in a table based on the join from other table e.g. for a customer calculate and update data values for Order values in from Item Price and Order table. below is an idea for doing this... Create Main table to be updated: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimCustOrderValue] ( [CustID] [int] NULL, [CustomerName] [nchar] ( 20 ) NULL, [OrderValue] [numeric] ( 18 , 2 ) NULL ) ON [PRIMARY] GO --Create other required tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemOrder] ( [OrderId] [int] NULL, [ItemId] [int] NULL, [CustID] [int] NULL [OrderQuantity] [int] NULL, ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemPrice] ( [ItemID] [int] NULL, [ItemName] [nc...