用于增量创建记录的脚本

Script for incrementally creating records(用于增量创建记录的脚本)
本文介绍了用于增量创建记录的脚本的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

Paul 为下面的问题提供了一个有用的脚本,但我想实际影响这些更改.如果我使用select语句,我只能看到它请帮忙解决这个问题

Paul provided a useful script for the issue below but I would like to actually effect the changes. I can only see it if I use the select statement Please help with this one

表名:Citizen

Firstname       Lastname    Telephone         Many other columns......
John             Smith      03907625212    
Andrew           Evans      0807452132    
Bill             Towny      05907122139  
Dame             Beaut      07894650569   

我有超过 150,000 条记录,其中电话号码需要采用设置格式(设置电话区号和递增顺序),即 01907000001、01907000002,如下所示.除了名字和姓氏之外,还有其他列都将保持不变.只有电话字段需要此转换.

I have over 150,000 records where the Telephone number needs to be adopted to a set format (set telephone area code and in incremental order) ie 01907000001, 01907000002 as shown below. There are other columns asides the firstname and lastname which will all remain unchanged..only telephone field requires this transformation.

理想情况下应该是这样的:

It should ideally look like this:

Firstname       Lastname      Telephone         Many other columns......
John             Smith       01907000001   
Andrew           Evans       01907000002  
Bill             Towny       01907000003 
Dame             Beaut       01907000004   

我将非常感谢您在这方面的帮助或指导.

I will really appreciate some help or guidance on this.

推荐答案

试试这个:

SELECT 
    [FirstName],
    [LastName],
    '01907' + --area code
    RIGHT('00000' + --middle padding for zero's
        CAST(ROW_NUMBER() OVER (ORDER BY [LastName]) AS VARCHAR) --incremental value
        ,6) AS 'Telephone' --6 characters plus area code

        --<< YOUR OTHER FIELDS
FROM 
    [AdventureWorks].[Person].[Person]

我使用 Adventure Works 只是为了测试它.

I used Adventure Works just to test it.

如果您希望它增加其他内容,请更改窗口函数中的 ORDER BY 子句.

Change the ORDER BY clause in the windowing function if you want it to increment by something else.

这篇关于用于增量创建记录的脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)