SQL Server 2000 + Sequential Primary Key
Oct 30, 2007
Hi,
I need a sequential primary key to store a document number.
Is IDENTITY(1,1) the best way to achieve this on SQL Server 2000?
If I have a great number of users on my application doing an insert on the DB at the same time, could this lead to any problem like it trying to insert the same PK for some of them? Can this happen, and if so what can I do to prevent this?
I've been reading about NEWSEQUENTIALID() but it's only avaiable on SQL Server 2005 and i'm still using 2000.
Another question.. should I avoid using NEWID() and use IDENTITY instead? I've been reading that newid() lowers the system performance because all values are non sequential.
Thanks!
View 3 Replies
ADVERTISEMENT
Dec 5, 2005
Hello, I have a table where I'm deleting the contents before populating the table with new data. I have an ID column that is autogenerating a sequential number. I would like to reset this number back to 1 when I delete the contents of the table. How can this be accomplished?
View 3 Replies
View Related
Sep 25, 2015
I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
View 2 Replies
View Related
Aug 9, 2014
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314
Desired out put:
From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
[Code] ...
View 6 Replies
View Related
Feb 13, 2015
The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.
Ex1:
CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051
Ex2:
CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
View 1 Replies
View Related
Aug 31, 2015
I am checking some ratio numbers for our system engineers, those are
Read/write ratio?
Random/sequential ratio?
Read/write block size?
For Read/write ratio, I am using below query,
SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'
[Code] ....
Random/sequential ratio, I googled but cannot find a similar query to get the result?
View 1 Replies
View Related
Dec 5, 2001
Is there a way to move the primary log file for a database to another drive without doing this with a restore database operation? I have added a second log log file but now I would like to delete the one that was created when the database was created.
View 1 Replies
View Related
Mar 25, 2008
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
View 4 Replies
View Related
Feb 4, 2015
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies
View Related
Oct 31, 2007
Hello,I am creating a new database and I was advised to use Sequential Guids.I was reading some information and, as far as I understood, I can use NEWSEQUENTIALID. This can be used when I have a uniqueidentifier column as the key of a clustered index to avoid fragmentation during insert. Ok, so I use NEWSEQUENTIALID instead of NEWID.But I will use LINQ most of the time instead of Stored Procedures.So can I specify in my tables scripts to use Sequential Guids when, for example, a record is created? And am I right when using Sequential Guids?Here is a part of my code:-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade Thanks,Miguel
View 2 Replies
View Related
Apr 8, 2008
In Foxpro I would often run sequential queries on data. Each query would store the results in a cursor, which would be the datasource for the next query. It was an easy way to write several simple queries to accomplish something complex.
Like this:
select <fields> from table1 where <condition> into cursor work1
select <fields> from work1 where <condition>
How do I accomplish this in TSQL?
View 4 Replies
View Related
Jan 25, 2007
Hi everyone I stumbled across this problem recently and have tried to figure out a good solution and have come up with nothing.
Environment: ADO.NET, ASP.NET 2.0, MS SQL 2000, C#
Problem: I have a set of data which I order according to two columns of data in the set. One column is a varchar or a date value (depending on what the user has chosen from GUI controls) and the other is an identity column. This dataset contains lots of data so its not feasible to pull the entire dataset to the client, also everything must be performed via ADO.net (no stored procedures). When the user selects one of the records I load another page and drill down into the record details (think of this as the record details page). This is fine and easy but on this record details page I would like to keep a Next and Previous button so users can move to the next record in the set (remember this set is sorted somehow on the previous page). My question is how can I know what next record should be? It would seem I need to attach a sequential number to the rows of data so I can easily grab the next one in the set. Solutions to this seem to make use of a temp table which I dont think is possible via ado.net.
Is there a decent performing way to do this through ado.net?
Thanks for all insight.
View 4 Replies
View Related
Feb 28, 2007
Hi all,I might be getting this all wrong but bear with me.
I need to create some kind of Unique field in my DB that is
nonsequential. This is because I need it to be difficult to guess
ids if you have an example in front of you.I have looked at
8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me
how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN)
)
The "T" serves to split the numbers for when I am converting back.
So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) )
------------------------------------------------------------------------------------------
3166 00031663 C5ET7BAF
3167 00031673 C5FT7BB9
3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?)
What do people think?
Thanks,Pete
View 4 Replies
View Related
Apr 4, 2008
I am currently trying to get a dataset of sequential decreasing calculations, but they all end up on one row. I need each of the secondary selects statements to show up as separate rows, any idea?Select
(select Rate * @Premium from ProfitLossTable where rateID =1)
, (select Rate * @Premium - @premium from ProfitLossTable where rateID =1)
,(select @premium - Rate * @Premium from ProfitLossTable where rateID =3)
View 3 Replies
View Related
Apr 27, 1999
I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
View 1 Replies
View Related
Mar 8, 2006
Hello--I need to create a SQl statement (SQL SERVER 2000) which will do the following:
read table1 sequentially
select every "nth" record
write each selected record to and output table
Your help is greatly appreciated.
thanks,
MB
View 3 Replies
View Related
Jul 27, 2006
I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
Some of the runs on the schedule have a note associated with them. I need
to be able to number these notes, so that they can be displayed in the report
bibliography style. When I populate the data in the sproc, if the "notes"
field in my table contains any data I display a 1, else it's a 0
Like:
SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
FROM MASTER_SCHEDULE
The problem is, I really need to display sequential numbers instead of 1's
If I create a base set with this:
CREATE TABLE #TMPRST (
RECID INTEGER,
PRODUCT VARCHAR(10),
QTY FLOAT,
NOTE_FLAG INTEGER)
INSERT INTO #TMPRST
SELECT 1, 'ABC123', 4, 0
UNION ALL
SELECT 4, 'DEF123', 5, 1
UNION ALL
SELECT 5, 'ABC456', 12, 0
UNION ALL
SELECT 13, 'PQR789', 10, 1
How do I go back and convert the note_flag column to read
... 0
... 1
... 0
... 2
View 14 Replies
View Related
Oct 17, 2012
We are integrating all our applications/databases into one application/database. During the transition phase, I need to create a number of views based on the new database that mimic the old tables of the old databases, so the old programs can continue to function until they are gradually replaced.
In one of the views, I need to generate a sequential number. The value is unimportant, as long as it is unique in the dataset; strictly spoken, it even doesn't need to be sequential:
eg:
SELECT * FROM myView
should give
Code:
col1col2...id
lalacar..1
bababike..2
....
zsrdpen..896
ghrtink..897
SELECT * FROM myView ORDER BY col2
should give
Code:
col1col2...id
bababike..1
lalacar..2
..
ghrtink..45
..
zsrdpen..396
....
The view is created based on a number of tables.
View 1 Replies
View Related
Mar 17, 2004
I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
View 4 Replies
View Related
Oct 25, 2013
I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.
Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?
View 3 Replies
View Related
Oct 27, 2006
this is a slight change to a fequently asked question around here. Ihave a table which contains a "sortorder" column where a user canspecify some arbitrary order for records to be displayed in. Usershave sometimes ordered records the way we used to number lines in aBASIC program (10,20,30, etc.). I'd like to do an update query and fixthis so that every record is in sequential order. I found an examplein this newsgroup of how to do this.However, I have a slight problem! Sometimes, the users duplicated thesortorders. So for example, I might have two records were thesortorder is 20. The query I found in this newsgroup does not work inthat case. Here is some code so that you can see what I mean.create table sorttest (label char(5),sortorder int)goinsert sorttest values ('joe',20)insert sorttest values ('dan',10)insert sorttest values ('jim',44)insert sorttest values ('tom',20)insert sorttest values ('jan',50)-- data dumpselect label, sortorder from sorttest order by sortorder-- I'd like to fix all of the sortorder fields so that they aresequentialupdate sorttestset sortorder = (select count(*)from sorttest subquerywhere sorttest.sortorder <= subquery.sortorder)-- note that tom and joe BOTH HAVE SORTORDER = 4select label, sortorder from sorttest order by sortorderdrop table sorttestThanks in advance for any help.
View 19 Replies
View Related
Sep 25, 2015
I have to increment sequential values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
View 3 Replies
View Related
Feb 7, 2008
Hi,
I've a column say page#. This has by default 4 pages which I'm numbering as 1,2,3,4. I'll be adding pages to this table and the number needs to start from 5 (since 4 pages are already present)
Page# is not an identity column. I want to know if theres a function which can do the trick.
Thanks for your help!
Subha
View 4 Replies
View Related
Aug 23, 2007
I couldn't find a topic suitable for testing this, so I thought I'd start one.
Here is one way to get the islands without a tally table.declare@test table (symbol char(3), dt smalldatetime)
insert@test
select'abc','01/01/1990' union all
select'abc','01/02/1990' union all
select'abc','01/03/1990' union all
select'abc','01/04/1990' union all
select'abc','01/05/1990' union all
select'def','01/03/1990' union all
select'def','01/04/1990' union all
select'def','01/05/1990' union all
select'def','01/06/1990' union all
select'def','01/07/1990' union all
select'ghi','01/01/1990' union all
select'ghi','01/02/1990' union all
select'ghi','01/06/1990' union all
select'ghi','01/07/1990' union all
select'ghi','01/08/1990'
selectsymbol,
min(dt),
max(dt2)
from(
selectt1.symbol,
t1.dt,
t2.dt as dt2,
(select count(distinct t3.symbol) from @test as t3 where t3.symbol < t1.symbol and t3.dt <= t1.dt) AS r
from@test as t1
inner join@test as t2 on t2.symbol = t1.symbol
wheret2.dt - 1 = t1.dt
) as d
group bysymbol,
r
E 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Feb 11, 2008
Hi Friends,
I need help from you.
I am working on SSIS packages for ETL purpose.
The version of SQL Server i am using is SQL Server 2005.
In Brief , the working of current ETL is as follows.
In ODS database i have 2 tables i.e Table_A & Table_B which gets loaded from another 2 staging tables A & B.
And using this 2 tables data will be loaded into a target table i.e Trg_A.
The ETL packages are executed by stored procedures by creating a job within the stored procedure.
The loading of the trg table is little tricky.
Before that loading of Table_A is implemented in a single SSIS package.
and loading of Table_B is been implemented in another SSIS package.
In the trg table there are two columns which will be getting updated as and when each table is loaded.
so for the first time if i run the package which is resposible for loading Table_A, it loads values into Table_A and once done it will updates (col1) in the target table.
Once after the complete of the execution of Package1.
Now i will kick off the second ssis package which loads the data into Table_B and updates the trg table's columns (col2).
Now the actual problem what i am facing is:
For loading Table_A and updating the col1 in Trg table i will be receving more than 5 excel file every month on weekly basis.
I cannot even gather all the files and run using a For-Loop counter.
So presently i am loading data excel file per week .
Similarly loading of table_B.
For a week if i am executing both the packages which loads the Table_A and updates the Trg(col1) and Table_B and updates Trg(col2), then i am getting a Deadlock Error and the entire ETL is getting messed up.
Now my requirement is , Eventhough the 2 packages are run in parallel , there could certain milli seconds time difference while start of the execution in Job Monitor.
I need to implement a Queing Mechanism which takes care of running the packages in a sequential manner rather than in parallel. i .e i need to ensure only one SSIS package is running in Job Monitor. Only after successful execution of either one the package, then only the second package should start its execution.
If we can implement such a queing mechanism , then my problem is solvedl.
I need some suggestions on this regard in implementing the Queing mechanism in a programatic approach using SQL Server Job Related MetaData Tables.
or else is there in server parameter or initialization parameters which can be set at Database level which suffice my requirement.
Any suggestions would be greatly appreciated.
Looking for sincere comments on this regards.
Thanks in advance.
View 4 Replies
View Related
Jul 23, 2005
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
View 9 Replies
View Related
Jul 23, 2005
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Jul 20, 2005
I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq
View 1 Replies
View Related
Apr 25, 2008
im sure there must be function for this but i cantfind it anywhere
essentially i want to do a Large insert on a table around 3000 records
which links to a table using 3000+ non sequential IDS
the content will be the same but bar the ids
roughly along the lines of
Code Snippet
insert into comment
values
('Some comment',date,commentypeid, non sequential List of IDs)
i could do this fairly simply in c# using a List<int>
Code Snippet
foreach (int ID in IDS)
{
insert into comment
values
('Some comment',datetime.now,1234, ID)
}
surely there is a a function
Thanks in advance
Jake
View 2 Replies
View Related
Feb 17, 2005
Hello everyone,
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
BlackCatBone
View 3 Replies
View Related
May 9, 2007
***** SQL Server 2005 ********
I have a table that needs to be updated with a sequential number based on criteria.
I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.
SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8
This is what the table should look like after the update in complete.
SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1
Can anyone HELP me please?
View 4 Replies
View Related
Jan 15, 2015
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
View 5 Replies
View Related
Aug 17, 2015
create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked) from table
View 5 Replies
View Related
