Capturing Value Of Identity Column For Use Later?

Jul 20, 2005

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @@IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

---------------------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@CurWeekReportedLineID = @@IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@WeekReportedID1,
@CurWeekReportedLineID,
@BeginDate1,
Estimate1,
@DummyHoursTypeID,
@DummyHoursType,
@DummyTaxCodeID,
@DummyTaxCode
FROM#EstimateLines;
------------------------

The #Temp table create via VB:
------------------------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
------------------------

--
PeteCresswell

View 2 Replies


ADVERTISEMENT

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Transact SQL :: Alter Non Identity Column To Identity Column

Aug 12, 2009

when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

How To Use Identity On Non-identity Column (with Concurrence)

Aug 1, 2014

I'm working with a third-party database (SQL Server 2005) and the problem here is the following:

- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).

- The PK of T is an Integer.

Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.

Considering that I can't change the PK, what is the best approach to solve this problem?

To sum up:

* I need to have processes in parallel inserting on T

* I can't change anything on T

* The PK is NOT an Identity

View 4 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

GUID As Primay Column And Identity Column

Jan 9, 2007

Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK  or it will bring some problems?
Thank you in advance for your help
 

View 3 Replies View Related

Alter A Column To Be The Table Identity Column

Aug 3, 2006

i have a table
table1
column1 int not null
column2 char not nul
column3 char

i want to script a change for table1 to alter column1 to be the table identity column. not primary.

View 5 Replies View Related

Making An Existing Column An Identity Column

Mar 5, 2004

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.


Ajay

WORD4LIFE
(http://www.word4life.com)

View 2 Replies View Related

To Make An Existing Column Become An Identity Column

Jul 20, 2005

Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY

View 3 Replies View Related

How To Bind A Column To Identity Column Of The Different Table.

Apr 2, 2007



Hi,



I have two tables table1 and new_table



Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .



And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.



This type of functionality is requirement for my project.



I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;



Please help me out to fix this issue



Thanks

Purnima

View 3 Replies View Related

Change An Int Column To Identity Column

Dec 17, 2001

Hi, I want to change an int column (not null) to identity column. I tried
the following:

alter table myTable alter column ID int identity(10, 1) not null

But it failed with the error message:

Incorrect syntax near the keyword 'identity'.

Can someone please show me the correct statement (if it exists)>

Many thanks.

View 4 Replies View Related

Trying To Get Identity Column

Nov 8, 2007

I have been trying to get the new sol_id that is added so I can use it in other code, but it is only returning 0. The new record is getting added to the table, but my varaiable myNewSolID is 0 in my response.write(myNewSolID)
I appeaciate any help!
Here is my steored procedure and my code:
 ALTER PROCEDURE [dbo].[AddTrackings]

@DropDate datetime,
@Comment nvarchar(100),
@DateEntered datetime,
@EnteredBy nvarchar(50),
@Sol_ID Int OUTPUT
AS
INSERT INTO tblTrackings (DropDate, Comment, DateEntered, EnteredBy)

VALUES (@DropDate,@Comment,@DateEntered,@EnteredBy)

SET @Sol_ID=SCOPE_IDENTITY()

RETURN


Sub ProcessTracking()
Dim myNewSolID As Integer

Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString

Dim MySQL As String = ""

If Me.chkDropSave.Checked Then
MySQL = "AddTrackings"
Dim MyConn As New SqlConnection(ConnectStr)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Dim InsertedInteger As New SqlParameter("@Sol_ID", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
Cmd.Parameters.Add(InsertedInteger)

Dim MySqlParamStart As New SqlParameter("@DropDate", SqlDbType.DateTime)
Cmd.Parameters.Add(MySqlParamStart)
MySqlParamStart.Value = Me.BasicDatePickerDropDate.SelectedValue

Dim MySqlParamAmtTo As New SqlParameter("@Comment", SqlDbType.NVarChar, 100)
Cmd.Parameters.Add(MySqlParamAmtTo)
MySqlParamAmtTo.Value = Me.txtComment.Text

Cmd.Parameters.AddWithValue("@DateEntered", Now())
Cmd.Parameters.AddWithValue("@EnteredBy", Profile.UserName)

Try
MyConn.Open()
myNewSolID = Cmd.ExecuteScalar()Response.Write(myNewSolID)

Catch ex As Exception
Response.Write(ex.Message)
Finally
MyConn.Close()
End Try

Else
End If
End Sub 

View 8 Replies View Related

Is Identity Column?

Apr 24, 2008

Hi to all,             Using sql query i want to check whether the column is Identity column or not? Please members reply me.         

View 2 Replies View Related

Identity Column - DTS

Mar 27, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

Unchecked Enable identity, do luck.

What would be the standard procs for this since this sounds like a very common
senario.
What I am expecting is to insert from source and SQL would take care identity column automatically.
Appreciate your help very much !

-Ivan

--------------------------------------------------------------------------------

View 1 Replies View Related

Identity Column

Sep 17, 2001

I want to know how to reset the identiy column when i delete a row from the existing table.

Example:

I have a table a with id (identity incr value 1) and name

I have 10 records inserted into it.,which would be from 1...thru...10 i delete a record say 5 then i have 1,2,3,4,6,7,8,9,10, now i want them to be rearranged to systamatic order like 1,2,3,4,5,6,7,8,9 how can i do this.


Thanks in advance,

Shaheen

"God Bless America"

View 2 Replies View Related

Identity Column

Feb 29, 2000

I have a table that has a field that will not accept null values. I was trying to create a new table with a new field that would accept null values. I would then drop the original table and rename the new table. The problem is my key field has the Identity column (Autonumber) turned on. This allows the recordID to be automatically generated. Is there anyway to bypass the Identity Column feature and still keep it there future records? I already tried the "with Nocheck Clause" but does not work.

View 2 Replies View Related

Bcp W/ IDENTITY Column

Nov 11, 1998

How does one bcp data INTO a MS SQL Server table that includes an IDENTITY column from a text file that does NOT include a value for the IDENTITY column? Surely, this must be a common process?

View 3 Replies View Related

Identity Column.

Apr 9, 2006

Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?

View 7 Replies View Related

Identity Column

Feb 9, 2004

How do you insert into a table that has an identity column aset of rows with predefined values for the identity column.
I have tried to Set Identity_insert off and then insert but it didnt work.
any ideas?

View 1 Replies View Related

Identity Column

Apr 3, 2006

can i add null to identity type column

View 5 Replies View Related

Identity Column

Mar 18, 2008

Hi frnds

I have a table with two columns eid , ename . eid set identity(1,1).
After inserting 10 rows how can we insert a user defined eid ?

need help

Thanks & Regards
Zakeer

View 2 Replies View Related

Identity Column

Jul 23, 2005

Hi,I would like a piece of advice.I have 3 foreign keys in a table used as primary keys for this table.Is it useful in that case to have just one identity column that wouldbe used as the unique primary key, thus no need to have 3 primary keys?Advantages vs Drabacks ?Regards

View 7 Replies View Related

RDA With Identity Column.

Nov 6, 2006

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks



James

View 11 Replies View Related

Identity Column

Sep 13, 2007



Hi,
I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.

View 3 Replies View Related

Identity Column

Oct 3, 2007

Consider a table by name "IdentityExample" having only one column "ID_Column" which is an identity column. Now How will you insert a row into this table???

Table Structure
----------------------


CREATE TABLE IdentityExample
(

ID_Column int IDENTITY(1,1)
)

View 4 Replies View Related

Reseting Identity Column Using SQL

Nov 11, 2006

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)
Thanks, radco

View 1 Replies View Related

Primary Key Vs Identity Column

Jan 22, 2007

What is the different between Primary key and Identity Column? and when should I them?
(Im just a beginner with databases...)

View 1 Replies View Related

Error In Identity Column

Aug 3, 2007

For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739
Any ideas why this would happen?
thanks

View 3 Replies View Related

Updating Identity Column

Sep 18, 2007

Please excuse my ignorance.  I've researched this and it appears I am asking to do something that is ridiculous, so please let me know what is wrong with my design (or my brain).  I'd like to update an ID number in a table.  It is an identity column.  In my solution I'm adding a lot of new entries into my table and deleting old ones.  Call me anal, but It's driving me nuts that my ID numbers are growing so large so quickly and that I have so many unused ID's.  If you were to look over my data ID's they would be something like 1,3,45,78,88,89,103,140,219.  What I'm trying to do is renumber my data so that my data currently at say ID# 1067 can be moved to the unused ID#2, etc.  So I either need a way to update an Identity column...or I need a way find the lowest unused number among a list of ID's.  So is there anyway to achieve what I am trying to do?  I see this guy had my same OCD issue (without a solution) :-(

View 3 Replies View Related

How To Add An Identity PK Column To A View

Nov 16, 2007

For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK. 
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList. 
 I am wondering how can I add an identity PK to my view?
TIA,Jeffrey

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved