May 7, 2012 at 5:50 pm
I have a CSV file that contains about 2,000 customer accounts. Unfortunately, the Customer IDs do not start from 1 and are not in order. As an example the Customers IDs are in this order after sorting (2, 3, 12, 19, 23, etc.)
Is it possible to write some SQL statement to fill the gap not necessarily updating the table! I just want to be able to export the data to Excel so I can further work on the data.
Customer Table Structure:
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
Fawad Rashidi
www.fawadafr.com
May 8, 2012 at 8:35 am
Try left joining from a Tally table to your data on export to fill the gaps.
Here is a derived Tally table you could use:
-- From: http://www.sqlservercentral.com/articles/Tally+Table/72993/
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
SELECT *
FROM cteTally;
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply