Identity Column Fill the Gaps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Identity Column Fill the Gaps

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2012-05-07 : 19:43:48

In my customer table not all the customer IDs are in sequence. Is there any way within SQL I can fill the gaps not necessarily by updating the table; instead, being able to export the data to a CSV file.

The records that are filled dynamically by the SQL statement, I would like to be able to add some dummy text to the customer name. So, when I use Excel to edit the CSV file, I can easily identity which records where filled by the SQL statement.

Here is the structure of the table in case you want to review:



USE [implantdirect]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 05/07/2012 16:42:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customers](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[AccessKey] [varchar](1) NULL,
	[Password] [varchar](64) NULL,
	[FirstName] [varchar](30) NULL,
	[LastName] [varchar](40) NULL,
	[CompanyName] [varchar](100) NULL,
	[BillingAddress1] [varchar](75) NULL,
	[BillingAddress2] [varchar](75) NULL,
	[City] [varchar](45) NULL,
	[State] [varchar](30) NULL,
	[PostalCode] [varchar](15) NULL,
	[Country] [varchar](30) NULL,
	[PhoneNumber] [varchar](35) NULL,
	[FaxNumber] [varchar](30) NULL,
	[EmailAddress] [varchar](75) NULL,
	[PaysStateTax] [varchar](3) NULL,
	[TaxID] [varchar](64) NULL,
	[EmailSubscriber] [varchar](1) NULL,
	[CatalogSubscriber] [varchar](1) NULL,
	[LastLogin] [smalldatetime] NULL,
	[LastModified] [smalldatetime] NULL,
	[PercentDiscount] [float] NULL,
	[WebsiteAddress] [varchar](150) NULL,
	[DiscountLevel] [varchar](1) NULL,
	[FirstDateVisited] [smalldatetime] NULL,
	[FirstOrderDate] [smalldatetime] NULL,
	[CustomerType] [varchar](1) NULL,
	[LoginAttempts] [int] NULL,
	[LastLoginAttempt] [smalldatetime] NULL,
	[LastModBy] [int] NULL,
	[Customer_IsAnonymous] [varchar](1) NULL,
	[IsSuperAdmin] [varchar](1) NULL,
	[news1] [varchar](1) NULL,
	[news2] [varchar](1) NULL,
	[news3] [varchar](1) NULL,
	[news4] [varchar](1) NULL,
	[news5] [varchar](1) NULL,
	[news6] [varchar](1) NULL,
	[news7] [varchar](1) NULL,
	[news8] [varchar](1) NULL,
	[news9] [varchar](1) NULL,
	[news10] [varchar](1) NULL,
	[news11] [varchar](1) NULL,
	[news12] [varchar](1) NULL,
	[news13] [varchar](1) NULL,
	[news14] [varchar](1) NULL,
	[news15] [varchar](1) NULL,
	[news16] [varchar](1) NULL,
	[news17] [varchar](1) NULL,
	[news18] [varchar](1) NULL,
	[news19] [varchar](1) NULL,
	[news20] [varchar](1) NULL,
	[Allow_Access_To_Private_Sections] [varchar](1) NULL,
	[Checkbox_For_New_Customers] [varchar](1) NULL,
	[Customer_Notes] [text] NULL,
	[SalesRep_CustomerID] [int] NULL,
	[ID_Customers_Groups] [int] NULL,
	[Custom_Field_Custom1] [varchar](50) NULL,
	[Custom_Field_Custom2] [varchar](50) NULL,
	[Custom_Field_Custom3] [varchar](50) NULL,
	[Custom_Field_Custom4] [varchar](50) NULL,
	[Custom_Field_Custom5] [varchar](50) NULL,
	[Custom_Field_Custom7] [varchar](50) NULL,
	[Custom_Field_Experience] [varchar](50) NULL,
	[Custom_Field_test] [varchar](10) NULL,
	[Custom_Field_License] [varchar](30) NULL,
	[Custom_Field_Custom8] [varchar](255) NULL,
	[Custom_Field_Custom9] [varchar](255) NULL,
	[Custom_Field_Custom10] [varchar](255) NULL,
	[Custom_Field_ACK] [varchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 19:51:52

what dummy text you need to add? is it based on some rule you need to add text?
can you show a typical sample data and explain how you want output to come out of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2012-05-08 : 10:54:23

No rule for the dummy text... I was thinking about adding <missing-record> for the first name column only. As an example, the customer table is like this right now:

CustomerID, CustomerName
1, John
3, Marry
5, Jeff

Then after the SQL statement is executed, I should have the following instead (without updating the database)

CustomerID, CustomerName
1, John
2, <missing-record>
3, Marry
4, <missing-record>
5, Jeff

--
Fawad Rashidi
Web Developer
www.fawadafr.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-09 : 12:36:44

There isn't really much point to reusing/filling those gaps. But, a simple search turns up a good article by Peso:
http://www.sqlteam.com/article/efficiently-reuse-gaps-in-an-identity-column

fawadafr
Starting Member

47 Posts

Posted - 2012-05-09 : 13:53:31

Thank you Lamprey

--
Fawad Rashidi
Web Developer
www.fawadafr.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources