Transact SQL :: Key Increment Even On Failed Insert Into On One Column
May 28, 2015
The reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null. Having null values would of contradicted the table where two columns "not null" are a requirement.
CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,
[code]...
View 2 Replies
ADVERTISEMENT
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
Jun 8, 2015
How to INSERT new column(Field) into MyTable
1. FirstName nvarchar(50)
2. LastName nvarchar(100)
3. Student bit Checked
4. CreateDate datetime
5. HwoOld float Checked
View 6 Replies
View Related
Aug 11, 2015
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
View 2 Replies
View Related
May 29, 2015
I have table with about 10000 rows, there is a column named Raw_XMLData is defined as varchar but data is xml format.
I try to insert into another table, if Raw_XMLData column has is valid xml data?
Is it possible to do in T sql?
View 2 Replies
View Related
Jul 26, 2015
I have the following 2 tables:
Table: classes Columns: classID, hp
Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.
create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go
View 4 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
Jul 18, 2015
I am trying to insert different number of columns into variables. This is what it does If I use a static columns.
declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)
#MonthtoDate temp table is created using a dynamic pivot query.
Data looks like this :
Account Type 1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13
Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3] from #MonthtoDate
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
View 6 Replies
View Related
Oct 22, 2015
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies
View Related
Jun 5, 2015
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
col1 [varchar](10) NOT NULL,
col2 [datetime] NOT NULL,
col3 [date] NOT NULL,
col4 [varchar](6) NOT NULL,
col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
1 SQLCHAR 0 10 "@|@" 3 Col3 ""
2 SQLCHAR 0 6 "@|@" 4 Col4 Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
Nothing works ....
View 7 Replies
View Related
Jun 23, 2015
How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.
Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')
Result from the above query
serial_no Scode Sdate SErr
1 1002 10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT
Desired SELECT STATEMENT Result with increment of serial_no (Max(serial_no+1)
Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'
View 3 Replies
View Related
Aug 4, 2015
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
View 11 Replies
View Related
Dec 19, 2006
I'm populating a table (TMP_SQL) with data from several other tables in our database.
I need to create through SQL commands a counter variable to fill TMP_SQL's KEY1 field. The data I'm pulling in from other tables does not have a field that is always 100% gaurenteed unique, so I need to create a counter for a key field.
Pseudo code:
insert into TMP_SQL(tmp_key1, tmp_general1, tmp_general2, tmp_number1, ....)
select @counter, someColumn, someColumn2, (select column3 from other table returning 1 row),...
from table1 where things = things.
How can I implement @counter = @counter + 1? Is this possible? I tried:
DECLARE @COUNTER INT
SET @COUNTER = 0
and then had @COUNTER = @COUNTER + 1 in my select statement; of course, I got the error about not being able to set variables in the same select as data-retrieval
Computers never do what I tell them to...
View 1 Replies
View Related
Jan 21, 2008
HI
I have table with 20 columns. One of the column(col1) in the table has value like below
Col1 -----------Col2
123------------- 2
123--------------4
123--------------6
453--------------2
898--------------2
723--------------2
723--------------4
How to do insert or update increment of 2 in a column (col2) for every same value in the Col1?
I am using Access. Is it possible to with the query ?
View 2 Replies
View Related
Oct 31, 2007
This is on SQL 2000 and 2005. I need to increment a variable on insert, but am having a hard time getting a workable solution:
declare @var int
set @var = (select max(id) from table1)
insert into table1
select val1,
@var +1 as val2
from table2
where blah and blah and blah
If I were trying to insert val1's of (5, 10, 20) with @var having an initial value of 100, the added rows should be:
val1 val2
5 101
10 102
20 103
Does anyone know how to do this without temp tables (or table variables)?
View 5 Replies
View Related
Jul 20, 2005
Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!
View 1 Replies
View Related
Nov 24, 1999
I am trying to update a new column called "sorting", with incremented values
strating at 1 for the first row and 2 for the second and 3 for the thirsd etc......., before doing that I need the table to be sorted using three
columns. I am using a cursor but it is not working I need your help.
Thanks in advance:
CODE:
set @ordering_count=0
declare review_test_cursor cursor scroll keyset for
select oid,decision_date,ranking,sorting,decision_id
from DECISION_FLAGS
order by oid,decision_date,ranking asc
open review_test_cursor
fetch next from review_test_cursor
while (@@fetch_status = 0 )
Begin
update DECISION_FLAGS
set sorting = CAST ((@ordering_count + 1) as VARCHAR)
set @ordering_count = @ordering_count + 1
fetch next from review_test_cursor
end
close review_test_cursor
deallocate review_test_cursor
View 2 Replies
View Related
May 2, 2008
How can I put an Auto increment on a non PK field?
for ordering contents items?
View 9 Replies
View Related
Oct 16, 2014
I have a column that is not set to auto increment "IDX and Im inserting 800 part numbers but i want the IDX column to start at IDX 400 and increment 1 time per part number that is inserted. how can i accomplished that task.
EXAMPLE:
IDX PART#
400 abcde
401 fghi
402 jklm
etc. and so forth until the last part# will have IDX 1200...
View 1 Replies
View Related
Oct 1, 1998
I have created a table that generates a sequential id and a
stored procedure that will return that id. The trouble is
no matter what I set the Seed or Increment values to, the
id will always start with #1 and increment by 1.
My table is BILLING_TIME_ID
Identity field BT_GEN_ID
(SEED 200, INCREMENT 1)
The sp is as follows:
CREATE PROCEDURE BT_NEXT_ID
AS
INSERT dbo.BILLING_TIME_ID DEFAULT VALUES
select count (*) from dbo.BILLING_TIME_ID
GO
I have double checked that Identity_Insert is set to off for
this table. (does this default to off unless it is set to on?) Since
there is only 1 field in the table, I don`t have any indexes set.
Thanks for your help!
Toni
View 1 Replies
View Related
Aug 22, 2006
Hi there,
With a Union all Query is there a way to have it also generate
an Auto Increment (number) column that is appended to the Union all results
?
View 2 Replies
View Related
Jun 4, 2015
I am trying to increment a column that I am creating in a select by 5's, example 5, 10, 20, 25.... first row starting at 5,
Something like
WITH CTE as
(
SELECT cast(5 as int) as myColumn from table
)
SELECT cte.myColumn + 5 from CTE
View 3 Replies
View Related
Feb 8, 2008
i've posted in the wrong forum, so im posting here
hi, im having problems to import data from my excel to a sql table.
in the excel file i have exact the same fields that i have in the table excepts the primary key which is an auto increment. When i try to import data, an error that i can't insert nulls into my auto increment column.
I put enable identity insert in the edit options, but still doesnt work.
can anyone help?
thanks in advance
View 1 Replies
View Related
Mar 10, 2006
hi,i am a beginner to ms sql server2000i have a tablecreate table ddd (a int, b int)by table structure is a bnow when i enter a value in b column suppose '2' in column bbext time when i insert a value in the column a i have to get the valuein b as 3 is thi spossible with triggersinsert into gdg values (1,2)a b1 2insert into gdg (a) values(2)a b2 3----------------> i have to get this 3 automaticallyis there any method to get thispls help mesatish
View 1 Replies
View Related
Aug 29, 2007
I have a column with consist of customer number.e.g.
c001
c002
c003
How do I add the character "c" to the auto-incremental number everything I add?
View 7 Replies
View Related
Oct 27, 2006
trying to change a column that is just a INT NOT NULL column, to have an auto-increment.
Code:
ALTER TABLE [ImpExpTables].[dbo].[ImpExp_eBayLocalNeedsDeleted]
ALTER COLUMN ID int IDENTITY(1,1) NOT NULL
just gives error at IDENTITY
View 7 Replies
View Related
Jul 20, 2004
Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?
I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)
but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .
Thanks,
Natalia
View 1 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Sep 5, 2007
Dear Sir,
the following code is copied from the SQL Server Help Example.
CREATE TABLE MyCustomers2 (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers2 (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers2 ALTER COLUMN CustId IDENTITY (200, 2)
It gives the following error.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'
Can U please guide me abt the error.
with regards,
wilfi
View 5 Replies
View Related
Mar 15, 2007
Hi members,
I am facing a grange error, I hv an asp .net application running with sql server 2000 on a web server.
one table say tblProducts, I have few colums in it with an ID Primery Key field which is auto increment.
I have around 1500 records in it, what i am seeing is that autonumbers are not comming in sequence. it is skiping few numbers after rendom intervals.
like
1
2
3
4
5
7
9
12
13
14
like this there is no error in the code as its only a simple insert query. can anybody tell me what could be the reason.
regards
Aloha
View 3 Replies
View Related
Jan 22, 2008
Hi
I am trying to use the identity data type ( column)
I am using examples from the book and management studio in sql server 2005.
I am using the identity property for the customer Id in customers table.I accidentatly executed the querry twice and I had two same record with different customer id numbers of 1 and 2 . I realised the problem and I had to delete the second record.
The problem is now even if I have deteted the 2nd record with customer id 2 , when I insert a new record the identity value ( customer Id) increments with a number after the value I deleted. i.e if I deleted a second row with customer id 2 ( identity 2) when I enter a new record it enters with a customer id of 3 and whenever i add a new record it increments from there.
So instead of first record have cust id 1 and second record customer id 2 etc , I get first record with cust id 1 , second record with cust id 3 , third record cust id 4 etc.
How can I get rid of this wrong values of identity values whenever I delete a record and try to add a new record?
Thanks
View 1 Replies
View Related
Oct 19, 2005
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
View 7 Replies
View Related
Mar 7, 2011
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
btw I have used this statement
SET
IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
View 4 Replies
View Related
