Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
declare @kk int set @kk=0 insert into tblSSAppsOrgEntityToEmployerMapDiffer (Id, OrgEntityCode, EmployerId, [Default],
[Code] ...
In above example Id is PK for Differ tbl and Temp tbl not having field related to this. thats why i have to take and increment that Id value manually.... but like above way i m getting error ..........
I'm starting out with Visual Studio 2008 and SQL Server CE and have come unstuck at the first hurdle.
I have performed the below simple steps: 1. I have created a table with an identity field in it. The identify increment is a positive value (1). This works fine and I can populate data to the table directly. 2. I drag the table adapter from the dataset to a form to create a datagrid form.
When I run this form, the field for the identify column populates with negative values and when I go to save the record the application errors.
Note: this only happens with SQL Server CE. I do exactly the same with a server based db and get positive increments via the form.
The simple-ugly solution is to just set a negative increment in the table to match what is going on via the form, but this strikes me as a cop out. It is most likely that I am overlooking something obvious and I would be very appreciative if someone could point me in the right direction.
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.
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
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>
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.
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
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)
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:
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
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
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
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.
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.
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.
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!
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?
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"
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.
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.
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
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
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.
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.
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.
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?
