SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sequencial insert


sequencial insert

Author
Message
Neel 7777
Neel 7777
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 84
I have list of Heads like A, B, C, D....
and like to insert in a table with three column COMCOD, SIRCODE, DESCRIPTION

Primary key SIRCODE nchar(12) and starts from '1802000001000' , next '1802000002000', next '1802000003000' and so on. The will look like

COMCOD SIRCODE DESCRIPTION
3306 1802000001000 A
3306 1802000002000 B
3306 1802000003000 c
3306 1802000004000 d
.
.
.
3306 1802000021000 rtrt
GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113880 Visits: 45440
Ok... so what's the question/problem?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114216 Visits: 41367
zahid_7777 (3/9/2013)
I have list of Heads like A, B, C, D....
and like to insert in a table with three column COMCOD, SIRCODE, DESCRIPTION

Primary key SIRCODE nchar(12) and starts from '1802000001000' , next '1802000002000', next '1802000003000' and so on. The will look like

COMCOD SIRCODE DESCRIPTION
3306 1802000001000 A
3306 1802000002000 B
3306 1802000003000 c
3306 1802000004000 d
.
.
.
3306 1802000021000 rtrt





Where is the "list of Heads"? Is it in a normalized table or in a single "cell" of a table or in a single variable. If either of the latter two, how is it stored? As a CSV, TSV, Fixed Width multi-entry or what?

As a bit of a sidebar and although I know you probably can't change it, I think it a bit insane to store numbers only values that need to be incremented (and, therefor, calculated) in an NCHAR column for several reasons. NCHAR usually makes no difference on numeric values, requires some special handling to increment, and wastes space because it requires 2 bytes per character. Your NCHAR(12) definition of this column occupies 24 bytes as opposed to just 8 bytes that would be used by a BIGINT.

Last but not least, your SIR Code appears to have a prefix and a sequence as a suffix. That also means that you have to use substring to split the 2 component parts out of the code for reporting. It would be a far better thing to store the 2 parts in separate columns and use a persisted computed column to put them together for display or reporting purposes.

If you can answer my questions as to "where" the "list of heads" is and what format it's in that I've identified in the first paragraph of this response, I'm positive that someone will be able to show you a relatively easy method to satisfy your requirements even if they, too, don't agree with the structure of the table.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neel 7777
Neel 7777
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 84
I like to insert SIRCODE in Sequence like x= x+1000

Then it look like
Comcod Sircode Sirdesc
3306 100001000 A
3306 100002000 B
3306 100003000 C
mister.magoo
mister.magoo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5384 Visits: 7871
Rauf Miah (3/10/2013)
I like to insert SIRCODE in Sequence like x= x+1000

Then it look like
Comcod Sircode Sirdesc
3306 100001000 A
3306 100002000 B
3306 100003000 C


Hey Rauf, you got the attention of two of the best people there...if you answer their questions you will learn so much...

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

    Group: General Forum Members
    Points: 114216 Visits: 41367
    Rauf Miah (3/10/2013)
    I like to insert SIRCODE in Sequence like x= x+1000

    Then it look like
    Comcod Sircode Sirdesc
    3306 100001000 A
    3306 100002000 B
    3306 100003000 C


    Yes. We know that. Please see my previous post. We need to know where the "List of Heads" of A, B, C, D "lives" and what format it is in.

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Neel 7777
    Neel 7777
    Valued Member
    Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

    Group: General Forum Members
    Points: 66 Visits: 84
    "List of Heads" of A, B, C, D data type is nvarchar(250)
    Lynn Pettis
    Lynn Pettis
    SSC Guru
    SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

    Group: General Forum Members
    Points: 50800 Visits: 38657
    Rauf Miah (3/10/2013)
    "List of Heads" of A, B, C, D data type is nvarchar(250)


    Where does this data come from?

    Cool
    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

    Group: General Forum Members
    Points: 114216 Visits: 41367
    Rauf Miah (3/10/2013)
    "List of Heads" of A, B, C, D data type is nvarchar(250)


    Thanks. I just got off from work and, if someone doesn't beat me to it (lots of capable folks on this forum), I'll give it a shot after work tomorrow night.

    Lynn is correct, though. Is the "list of heads" in a table column that way or just in a single variable?

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Sean Pearce
    Sean Pearce
    SSCrazy
    SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

    Group: General Forum Members
    Points: 2202 Visits: 3432
    Like this?

    CREATE TABLE MyTable
    (COMCOD SMALLINT,
    SIRCODE BIGINT IDENTITY(1802000001000, 1000),
    DESCRIPTION VARCHAR(255));
    GO

    INSERT INTO MyTable (COMCOD, DESCRIPTION)
    VALUES
    (3306, 'A'),
    (3306, 'B'),
    (3306, 'c'),
    (3306, 'd');
    GO

    SELECT
    *
    FROM
    MyTable;





    The SQL Guy @ blogspot

    @SeanPearceSQL

    About Me
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search