Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.
View 9 RepliesI have a Column named 'Series' and a column named 'Linenumber'. They look like this:
Series Linenumber
234 NULL
234 NULL
235 NULL
235 NULL
234 NULL
234 NULL
235 NULL
235 NULL
236 NULL
236 NULL
236 NULL
And I want to run a query and make it look like this:
Series Linenumber
234 1
234 2
235 1
235 2
234 3
234 4
235 3
235 4
236 1
236 2
236 3
So basically I want Linenumber to be an increment column but to increment seperately for each value of Series. RDBMS is Microsoft SQL Server 2008.
Here is a piece of code I found in an application I'm supporting.
Problem: We're seeing duplicate numbers and I think this code may be the source of it.
At first glance I did not understand how it worked.
It locks a table <begin tran> , updates a number, reads that number, then commits. This code appears to work - it returns the newly added number.
My Question - How? How can this code return a value that is not "Committed" to the database yet. Please critique..... I have an alternate method, please comment on it as well. Thanks for your input.
--THIS IS WHAT I FOUND....
CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT
If @@ERROR <> 0 OR @@ROWCOUNT <> 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT
If @@ERROR <> 0 OR @@ROWCOUNT <> 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN
My Newly suggested method. Using Implicit transaction.
I believe this is more "concurrency" friendly. Do you?
Begin loop until success
Read Number
Update to Number+ 1 WHERE number is the one I just read
If row was updated, use this new number and set success flag
Loop
Hi All,
Is there any option to get the velue of auto increment number before inserting record.
My problem is that I want to get the auto increment number, for this I am using MaxId function, but If i have deleted the some rows from table, I could not get the actual.
For example there are 20record s in the table I have delete the last 3 records now I have the last value of Identity is 17. When I used the Maxid function It gives me the 18 number. But I need 21.
How?
help required.
Thanks
Navi
will it be possible to increase number as below automatically000000010000000200000003....whenever the row is inserted, number will be increased like aboveformat.which data type should I select and do some other setting to recordlike that?thanks
View 4 Replies View RelatedHI,
I have one master table and multiple detail tables. The primary key of the master table is an auto-increment number, which is a foreign key in those detail tables. I am wondering if SQL Server allows us to get the next available auto-increment number of the master table up front. Thanks a lot.
Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?
In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?
Thanks a lot.
writing the procedure for incrementing the number based on alphabets from A-Z
for example:
if alphabet is A then increment should be A001,next A002..etc
if alphabet is B then increment should be B001,next B002...etc
.
.
.
.
if alphabet is Z then increment should be Z001,next Z002...etc
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
[code]....
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
Hi folks!This might be a stupid question but none the less...I have a table to store information. This table as a id field which is set as a unique identifier, only when I enter data into all the other fields in this table it throws back an error saying that the id field cannot have a null value. Fair enough. But it should auto-increment every time a new entry is made into the database? Is there a way to make the field auto-increment everytime a new entry is made or do I need to do it manually by adding one everytime? It seems like it would be a normal feature in the database? Is there not a setting somewhere?
View 3 Replies View RelatedHi, I'm specify the size of the interger dataypes in my code behind files; e.g.
commad.Parameters.Add("@SomeID", SqlDbType.Int, 4)
I know it may seem silly, but I have to ask:
Will specifying ths size (i.e. 4) eventually stop the auto-icnrement of the underlying primary key SomeID in the SQL Server 2005 database?
What restriction, if any, does this place if the next SomeID value is 10000, which is 5 digits? or 999999, which is 6 digits?
Thank you
I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.
Is there any way to know the number of current connections to an SQL Server Database? Also how can one change the max pool size (for the number of connections allowed to the database)?
Thanks.
Our application has a table, which is populated via ADO.Net from C# with data originating from a C++ COM call. Today I encountered an entry that is C++ code for an undefined value: -1.#IND stored in the database. However, I could only discover what was stored in the table by Casting the value to a varchar -- simply selecting returned an error.
Is this expected behavior or a bug? It does not seem correct that SQL Server should store a value that cannot be displayed. In essence, either the value should not be allowed in the table because it violated the domain or SQL Server ought to have a way to display it with a Select *.
As fas as our application is concerned, we will be masking these values -- initially by ignoring them in the queries and eventually the loading program will convert to null.
Hye all.. ;-)Question : 1. I try to Retrieve "00012" number from SQL Database..But the Return value is "12"..Wht I need is "00012" not "12". How I gonna do that?2. Because why..I need to save again that number in another Table. I'm using Data Type as "nvarchar(50)". Thank you.
View 4 Replies View RelatedI am setting up a database which schedules production and tracks inventory of items on a daily basis. The scheduler may put in 100 identical entries (apart from the identity column) of an item with its corresponding quantity. My problem is, if there is a shipment of product (a subtraction of quantity from the database), how can I delete a specified number of rows where the inventory listing is 100,000 pcs? I think the DELETE TOP(r) command will work but I don't know how make the command into an actual variable. Maybe there is another way too...
My current not-working try; I look at the product desired to delete, figure out how many rows to delete, and since it is not always an integer, figure out a quantity to add back in. The addition part works fine but delete command needs work. Any help is appreciated.
int InvRows = 0; decimal RealInvRows = 0; decimal AddQty = 0; int preAddAmount = 0; protected void DelInv_Click(object sender, EventArgs e) { Label TotProdSum = (Label)DetailsView2.FindControl("TotProdSum"); Label RowQty = (Label)DetailsView3.FindControl("RowQty"); int SubQty = Convert.ToInt32(ShipQty.Text); InvRows = SubQty / Convert.ToInt32(RowQty.Text) + 1; RealInvRows = SubQty / Convert.ToDecimal(RowQty.Text); AddQty = (InvRows - RealInvRows) * Convert.ToInt32(RowQty.Text); IntLbl.Text = Convert.ToString(InvRows); RealLbl.Text = Convert.ToString(RealInvRows); preAddAmount = Convert.ToInt32(AddQty); AddAmount.Text = Convert.ToString(preAddAmount); for (int r = 0; r <= InvRows; r++) { forWhile.DeleteCommand = "DELETE TOP (r) FROM Inventory WHERE (Inventory = @Inventory)"; forWhile.DeleteParameters.Add("Inventory", RowQty.Text); forWhile.Delete(); forWhile.DeleteParameters.Clear(); } forWhile.InsertCommand = "INSERT INTO Inventory(Dte, Product, Inventory) VALUES (@Dte, @Product, @Inventory)"; forWhile.InsertParameters.Add("Inventory", AddAmount.Text); forWhile.InsertParameters.Add("Product", InvProdDDL.Text); forWhile.InsertParameters.Add("Dte", Date.Text); forWhile.Insert(); forWhile.InsertParameters.Clear(); }
I use SQL 7.0 server to build large scaled Web application.
I want to know that reasonable number of database in single machine. I know
single machine can get a 32,767 database in SQL 7.0.
We think database deploying method as following two cases.
Case 1)
We will provide multiple web application services and each service will be put into each database. Some table in a database will get about 1,000,000 records. 1,000,000 users will access each database. The number of services will be 100.
Case 2)
Or, we group 4~10 services and these group put into one database and another group of services put into another database.
What is the best database deplying method that reliable to performance and maintenance ?
I am new to SQL Server 7 and am not sure how to handle setting up/importing FoxPro databases. The consultants that setup the database before me have combined all of the FoxPro databases into one big database in SQL server. The reasoning was that for user applications to have access to different databases in SQL they would need a connection for each database they are using, so if all the data is put into one database then the user would only have one database connection instead of 6 or 7.
Does this make sense to anyone? Someone else thought that this might have been true is SQL Server 6.5 but not in SQL Server 7.0.
Any help will be appreciated.
Mike
Hi,
I need to retrieve the version number of SQL database. Does anybody know how to retrieve it?
Thanks and Regards
Amol
Is there max number of Database Master Keys that can be used in a single instance of SQL Server 2005?
If I have 12 unique client databases that all need a unique Database Master Key, can this be accomplished?
Thanks ALL!
Is there a limit on number of symmetric keys/asymmetric keys/certificates that can be stored in a database?
Is there any effect on performance of the SQL Server if I have too many (few hundreds) symmetric keys in the database?
thanks
OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).
Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?
I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.
Is there any way to retrieve partial contents of a database column? For example, say a column holds 5,000 characters, but I only wish to retrieve the first 50.
Thanks
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
How can I allow users to input numbers with commas into a database field with an 'int' datatype without getting this error, 'Input string was not in a correct format'?
View 3 Replies View RelatedI saw a previous request for help, but I did not see a solution (other than deleting and re-initializing the dump.
I'm copying a database from one server to another and I repetedly get this error when I try to restore from the .bak file I create on backup. The host machine for the database is a workstation (no RAID). The Destination does have RAID, but is funtioning properly (according to the SysOps and in testing file copies). I've tried copying the .bak file to several different drives several different times and I get the same error. I also tried to back-up a database on the Destination server and restore the .bak file to a newly created database and I get the same error.
Anyone solve this problem?
I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.
Thanks,
Paulo
To all,How to backup a database into a number of smaller files ?For example, can I can fully backup a DB of 10 MB into 10 files (each 1MB)???The problem I've met is that the DB backup file is too large, over 4GB, and even Winzip can't compress it (after compressing, around 80 %of compression rate is possible)Thanks![color=blue]>From Jason (Kusanagihk)[/color]
View 11 Replies View Related Hi, i have a data base in SQL 2005 and i it has many tables with confidencial information Like Social Security numbers, Credit card Numbers, etc. And i would like to encrypt this information or make something to make this info invisible to any hacker, or any person that logs on the server.
Here are the limitations:
1 - I cant make any changes in the front end, so if i encrypt the data, I have to make all the modifications in the database, and if the front end needs some encrypted information, the database should dencrypt it.
Any one has any idea ???
Best Regards.
I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type
*i am using VS 2005 and VB language
Hi,
Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...Ashok kumar.