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
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
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.
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?
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 RelatedI 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 RelatedI 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 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?
View 6 Replies View RelatedHere is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?
e.g given
intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35
change it to:
intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35
I think it might need a stored proc..
Many Thanks
greg
I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
[Code]....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
I want to assign consecutive numbers to a block of data where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
1 2011-07-31 00:00:00.000
[Code] ....
My Attempt:
WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER
(
PARTITION BYTestId
[Code] .....
Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
1 2011-07-30 00:00:00.000 3
[Code] ....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedI have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
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>
How do I accomplish this in TSQL?
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)
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
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
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.
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 RelatedI 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.
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
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?
View 3 Replies View RelatedI 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"
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.