Hello,
I created a table with the identity column "ID". It will increase by 1 when I insert a row to the table.
However, when I wanted to publish my database with my application, I deleted all the rows in my table. At this time, I wanted the identity to begin with 1 again. Can I do it in sql server management studio or is there other ways to do it?
Thanks.
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
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
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick ' Insert the order and order lines into the database Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand
Try conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans
' set the order details cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)" cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@State", SqlDbType.VarChar, 50) cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15) cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
cmd.Parameters("@MemberName").Value = User.Identity.Name cmd.Parameters("@OrderDate").Value = DateTime.Now() cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text cmd.Parameters("@Total").Value = Profile.Basket.Total
Dim OrderID As Integer OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ? ' change the query and parameters for the order lines cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)" cmd.Parameters.Clear() cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int) cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int) cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int) cmd.Parameters.Add("@Price", Data.SqlDbType.Money) cmd.Parameters("@OrderID").Value = OrderID
For Each item As CartItem In Profile.Basket.Items cmd.Parameters("@ProductID").Value = item.ProductID cmd.Parameters("@Quantity").Value = item.Quantity cmd.Parameters("@Price").Value = item.UnitPrice cmd.ExecuteNonQuery() Next ' commit the transaction trans.Commit() Catch SqlEx As SqlException ' some form of error - rollback the transaction ' and rethrow the exception If trans IsNot Nothing Then trans.Rollback() End If ' Log the exception Throw
Finally If conn IsNot Nothing Then conn.Close() End If End Try ' we will only reach here if the order has been created successfully ' so clear the cart Profile.Basket.Items.Clear() End Sub
How do i get identity from stored procedure? I CANNOT GET THE ID I CANNOT RETURN ANYTHING AT ALL! WHY?
This is last three lines of stored procedure
SELECT ID = SCOPE_IDENTITY()
RETURN
GO
I get ABSOLUTELY nothing I can't get anything to come back from by stored procedure to my ASP page. I have a "RETURN_VALUE" parameter, that's not the problem. There's something really stupid about this stored procedure that since I've inserted records into a table then from that point on I cannot return ANYTHING! WHYYYYYY???
Sorry, I am SO VERY frustrated. Can anyone help me?
mellamokb
Below is the snippet of code that matters. Currently Figure 1 works great. I need to make Figure 2 work. Notice the Identity Seed vaue I need changed. Any ideas?
FIGURE 1:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY (1, 1),
Figure 2:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY ((SELECT NBRCOLUMN FROM TABLE WHERE BLAH = 'BLAH'), 1),
I need this desperately...
Thanks in advance.
Joe
Hi
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Björn
Hi,
How can we use two identity column (with identity property) in table ?
GK
gk
I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.
I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001
when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA
I have fixed length records (167 bytes) in a .txt file and want to loadthis data exactly as is into a staging table where I hope to be able tolater get at selected columns using the SUBSTRING function. Here is mytarget table:CREATE TABLE JJA_BCP_NHO_DAT (RecID int IDENTITY(1,1) NOT NULL, Data VARCHAR(167) NOT NULL)Here is my bcp command:bcp DevMDW.dbo.JJA_BCP_NHO_DAT inN:RawData_MDSPurchaseUnzipArea2005_08_Aug_0149 7407.txt -f bcp.fmtHere is the .fmt file:8.011 SQLCHAR 0 167 "" 2Data SQL_Latin1_General_CP1_CI_ASHere are the first 3 lines of data in the .txt file (note how 1st 2bytes are blank; last 2 bytes of each record are 78, 79, 80):02160168C013CMA20050819 328UUU AGAWAM 36422005072901001 BERKSHIREBANK 25013 7801940155C001 MA20050805 254UUU AGAWAM 28072005071801001 WEBSTERBANK 25013 7902350188C014CMA20050729 067UUU AGAWAM 24812005070701001 FIRSTPIONEER FARM CR 25013 80The bcp command runs OK but the output is "shifted" in the columncalled DATA in the table. The IDENTITY column looks good in all rows,but only the first row is OK in the DATA column. Starting in the 2ndrow, all bytes are truncated or shifted by what appears to be 2characters. This is hard to show with pasting results but I ran thisquery:SET ROWCOUNT 10SELECT RecID, SUBSTRING(Data,1,10) as FirstTen, SUBSTRING(Data,158,10) as LastTen, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT1 02160168 25013 781672 019401 25013 167379 0235CR 250116743 80 01 CO 251675013 80 CORP 167625013 80 REG SYS 1677 25013 98ANK 1678 25013 VGS BK 1679 2501 21 MTG CO16710RP 25RYWIDE HOM167Every line in the .txt file ends after column 167 with x'0d0a'. I'vetried a field terminator of
and that produces this error:SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encounteredin BCP data-fileThanks in advance for some help on this.
Hello all,
I fixed a after update trigger with sql server 2005 and I need to return the last identity updated in the table to insert it into another table. How can i get the last Identity updated in a table?
Please help ..
Sms
I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.
Thanks
Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...
SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.
Is there any way to work around this?
Thanks for your help.
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
@PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
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 !
What would be the standard procs for this since this sounds like a very common
senario.
Appreciate your help very much !
-Ivan
I created table
create table t1
(
oid int identity(1, 1),
f tinyint
)
and need way to fill it as fast as possible with say 100000 sequential values. Field f may stay = 0 but oid should be raising from row to row. How can I do it in MSSQL 2000?
I'm sure this is a common problem but I can't find any relevant info on this site
I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.
e.g. a simplified version...
TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)
TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)
Runing the insert statement
INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2
results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?
thanks
neill
Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?
View 4 Replies View RelatedHi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
View 3 Replies View RelatedHi,
I have a table with 1000000 records i try to add a field with following`spec.
[mkey] [int] IDENTITY(1,1) NOT NULL
I get the following meassage:-
yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Then I Have the following error:-
Time out error
- How is the best way to copy a large table from one to anther.
regards
HI,I have an SQL Server table with only 1 column. That column is anidentity column. How can I insert a row in this table using SQLsyntax?I tried insertinto T_tableName () values ()and a few other options, but I can't seem to get it to insert.ThanksAlain
View 1 Replies View RelatedI've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer
View 2 Replies View Related
Is there any way to remove IDENTITY property on particular table? I tried removing IDENTITY property using Manangement studio, but this operation behind the scene use migration concept that is by creating tmp table and then populating with data; droping the orginal and renaming the tmp back to original.
Second, i want some kind of generic solution using certain system table like aya.sysobjects, sys.identitycolumn etc such a way that i should be able to remove the idenity property from all of the table accross a database.
Mandip
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
View 8 Replies View RelatedI have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
View 3 Replies View RelatedHow can I alter a table turning ON or OFF an IDENTITY field ?
for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?
+
Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
Thanks in advance!
In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table. The purpose is to get a list that looks like this after the insert:
RowID valueID1 348972 345223 94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable variable?
hi folks,
How to disable/enable an Identity Column in a table with the help of an sql
statement.
Thanks in advance.
Vineet Agarwal