Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sequencial insert Expand / Collapse
Author
Message
Posted Saturday, March 09, 2013 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 AM
Points: 34, Visits: 63
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



Post #1428869
Posted Saturday, March 09, 2013 5:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025
Ok... so what's the question/problem?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1428871
Posted Saturday, March 09, 2013 7:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 32,930, Visits: 26,819
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1428889
Posted Sunday, March 10, 2013 12:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 AM
Points: 34, Visits: 63
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
Post #1428959
Posted Sunday, March 10, 2013 4:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:07 PM
Points: 1,308, Visits: 3,900
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




Post #1428966
Posted Sunday, March 10, 2013 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 32,930, Visits: 26,819
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1428990
Posted Sunday, March 10, 2013 6:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 AM
Points: 34, Visits: 63
"List of Heads" of A, B, C, D data type is nvarchar(250)
Post #1429045
Posted Sunday, March 10, 2013 9:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 21,633, Visits: 27,490
Rauf Miah (3/10/2013)
"List of Heads" of A, B, C, D data type is nvarchar(250)


Where does this data come from?



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)
Post #1429057
Posted Sunday, March 10, 2013 10:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 32,930, Visits: 26,819
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1429061
Posted Wednesday, April 10, 2013 2:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340
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;



http://thesqlguy.blogspot.com/
Post #1440691
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse