Sqlsever the Excel Data import table, the ID of the table is formatted for generation.

1, Query itemlist table.

SELECT * from Dbo.itemlist

< chart i >

ItemList Total 28 data

Now I want to implement, import from Excel data itemlist This table, require itemId inside the value conforms to p********* format, and is then increment, as in the new should be P000000029,

But this itemid is generated by the program, how to automatically generate when inserting, p000000030,p000000031 ... Format it.

1. The Excel data format is imported into the database, and the table temp is generated.

2, Query the temp table, query out the result set according to itemlist existing data as follows:

SELECT *

From (SELECT row_number () over (ORDER by itemId) as number,
*
From Dbo.temp
) T

Note: Where number is the line.

< figure II >

Insert data from figure two into itemlist, as follows SQL

INSERT into [Electrolux]. [dbo]. [ItemList]
([ItemId]
, [ItemName]
, [ItemGroup]
, [Stdprice]
, [Splprice]
, [IMAGESRC]
, [status]
, [OrgId]
, [OrgName]
, [remark]
, [creator]
, [CreateDate]
, [Lastupdator]
, [Lastupdate]
, [EndDate]
, [Limitcount]
, [TotalCount]
, [Packagegiftname])

SELECT Dbo.fn_getproductid ((Row_number () over (ORDER by [Model])) as ItemId,
[Model] As ItemName,
"As ItemGroup,
[Retail Price (RMB)] As Stdprice,
[Price Offer (RMB)] As Splprice,
"As IMAGESRC,
' Y ' as status,
As OrgId,
"As OrgName,
[Product Name] As remark,
' Admin ' as creator,
GETDATE () as CreateDate,
' Admin ' as Lastupdator,
GETDATE () as Lastupdate,
DATEADD (Mm,2,getdate ()) as EndDate,
NULL as Limitcount,
NULL as TotalCount,
' As Packagegiftname
From [Electrolux]. [dbo]. [Temp] ORDER by model ASC

Return a specific appropriate Itemid function as follows:

Use [Electrolux]
GO
/****** object:userdefinedfunction [dbo]. [Fn_getproductid] Script date:01/15/2015 16:52:36 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--Author:<[email protected]>
--Create Date: <2015-01-08 10:15:46.123>
--description:< Returns a product in a specific format based on the line number id>
-- =============================================
ALTER FUNCTION [dbo]. [Fn_getproductid] (@rowNumber INT)
RETURNS VARCHAR (100)
As
BEGIN
DECLARE @maxId INT--the largest ID
DECLARE @maxIdText VARCHAR (100)--return format

SELECT @maxId = CONVERT (INT, REPLACE (ISNULL (MAX (itemId), ' P0 '), ' P ',
") + @rowNumber
From Dbo.itemlist
SELECT @maxIdText = ' P ' + REPLICATE (' 0 ', 9-len (@maxId))
+ CAST (@maxId as VARCHAR)

RETURN @maxIdText


END

Finished, often when inserting data, the ID of a particular format can be generated in this way.

Sqlsever the Excel Data import table, the ID of the table is formatted for generation.