Insert A Field (IDENTITY) Into Table
Hi,
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
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Retrieving Identity Field From Table On INSERT
I 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 Replies !
View Related
Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
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
View Replies !
View Related
Alter Table For Identity Field
Hi all, Pls help on this one, I think should be very simple, but I donno how to do it..:( anyway, here is my problem, I like to replicate a DB, and there is some table which got identity field on it, so I can't replcaite those, and as I read the help, it said that if I put in the "NOT for replication" option for the identity field, then it should work, my question is how can I alter the table for the alter table function? can someone pls tell me the sctipt how to do it? all i know is: Alter table <table_name> <column_definition> identity(1,1) not for replication but what is the column_definition? I donno what they mean here... pls help ppl.. an another question (sorry for too many question), if that identity is replicated, is that mean from the publisher inserting then the number will replicate to the subscriber right, but what happen for the subscriber doing the insert, will that just replicate the number back to the publisher? and what happen if they do insert at the same time, I know there will be a conflicts and it can be solve somehow, but can it be auto add to the next auto number, so it won't be a conflict but in fact both record gets in? is the possible? thanx for all the help ppl. Thanx, Westley
View Replies !
View Related
Generate Identity Field On Existing Table
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!
View Replies !
View Related
How Can I Alter A Table Turning ON Or OFF An IDENTITY Field ?
How 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)
View Replies !
View Related
Problem DTSing Into Table With Identity Field
Hello everyone, I have a table X that has two columns (names have been changed to protect the guilty and innocent alike): ID which is an identity column, and Data which is a varchar. I am trying to DTS data from a text file into this table. I want the identity column ID to auto-populate and each row of information from the file to be stored in the Data field. I am creating the DTS package using the wizard in Enterprise Mgr. No matter what I try, I keep getting the following error: Cannot insert the value NULL into column 'ID', table 'X'; column does not allow nulls. INSERT fails. Any advice on what I need to do to get this working? It seems like such a simple thing and I'm getting very frustrated. :eek: Thanks in advance. Cathy
View Replies !
View Related
Insert Into A Table With Identity
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
View Replies !
View Related
INSERT Into Table With IDENTITY Column
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
View Replies !
View Related
Get Next Unique ID From A Table Before Insert @@identity / Sequence
How do I get the next int value for a column before I do an insert inMY SQL Server 2000? I'm currently using Oracle sequence and doingsomething like:select seq.nextval from dual;Then I do my insert into 3 different table all using the same uniqueID.I can't use the @@identity function because my application uses aconnection pool and it's not garanteed that a connection won't be usedby another request so under a lot of load there could be major problemsand this doens't work:insert into <table>;select @@identity;This doesn't work because the select @@identity might give me the valueof an insert from someone else's request.Thanks,Brent
View Replies !
View Related
BULK INSERT Into Table With Identity Column?
I have a file I'm trying to do some non-set-based processing with. Inorder to make sure I keep the order of the results, I want to BULKINSERT into a temp table with an identity column. The spec says thatyou should be able to use either KEEPIDENTITY or KEEPNULLS, but I can'tget it to work. For once, I have full code - just add any file of yourchoice that doesn't have commas/tabs. :)Any suggestions, folks?--create table ##Holding_Tank ( full_record varchar(500)) -- thisworkscreate table ##Holding_Tank (id int identity(1,1) primary key,full_record varchar(500)) --that doesn't workBULK INSERT ##Holding_TankFROM "d: elnet_scriptspsaxresult.txt"WITH(TABLOCK,KEEPIDENTITY,KEEPNULLS,MAXERRORS = 0)select * from ##Holding_tank
View Replies !
View Related
Insert Query On Table With Identity Column
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.tbl_appointment-------------------1. appt_id (pk) --- identity column, seed 25, increment 12. date_id3. time_start4. time_end5. appt_details6. lkp_emp_idPrivate Sub btnAddAppts_Click()On Error GoTo Err_btnAddAppts_ClickDim strsql As StringDoCmd.SetWarnings Falsestrsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"DoCmd.RunSQL strsqlDoCmd.SetWarnings TrueDoCmd.CloseExit_btnAddAppts_Click:Exit SubErr_btnAddAppts_Click:MsgBox Err.DescriptionResume Exit_btnAddAppts_ClickEnd Sub I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'. It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."
View Replies !
View Related
Can't Insert Rows In Table With Identity Column
Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error? Can anyone help??? Thanks!
View Replies !
View Related
Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
I have the following query in sql 2005: PROCEDURE [dbo].[uspInsert_Blob] ( @fName varchar(60), @fType char(5), @fID numeric(18, 0), @bID char(3), @fPath nvarchar(60) ) as DECLARE @QUERY VARCHAR(2000) SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document) SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB) AS Document" EXEC (@QUERY) This puts some values including a pdf or .doc file into a table, tblDocTable. Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport. tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable. Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport. Any ideas how to do this?
View Replies !
View Related
Select Based On User Identity Name Included In Insert Into Second Table
I am having issues getting this to work. I have the user login to a page to put in a request for vacation. When they login, I have a label that isn't visible that is equal to their User.Identity.Name. I select the user from the employee table where the username = the label User Identity Name and pull in the emp_id which is the primary key that identifies the user. I need to insert the request into the request table with the emp_id from the select statement, without showing the em_id on the screen. I tried using a hidden field and assigning the emp_id as the value, but it isn't working. Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I really appreciate any help. Thank you!
View Replies !
View Related
Cannot Insert Explicit Value For Identity Column In Table 'Clients' When IDENITY_INSERT Is Set To OFF
Code Snippet Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click Dim addclient As New newclient() newclient.ClientID = 0 Dim result As DialogResult result = addclient.ShowDialog If result = DialogResult.OK Then Dim Client As clients_and_sitesDataSet1.ClientsRow Client = Clients_and_sitesDataSet1.Clients.NewClientsRow Client.ClientID = addclient.ClientID Client.Clientname = addclient.Clientname Client.Street_and_number = addclient.Street_and_number Client.Zipcode = addclient.Zipcode Client.Place = addclient.Place Client.Phone = addclient.Phone Client.Email = addclient.Email Client.Contact_person = addclient.Contact_person Clients_and_sitesDataSet1.Clients.AddClientsRow(Client) ClientsTableAdapter.Update(Client)------------------------------problem MessageBox.Show("New Client is saved") Else MessageBox.Show("User cancelled operation") End If addclient = Nothing End Sub Code in VB (2005 express edition) Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line. when i try to run the program, i get an exeption Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out. And please give me specific guide lines on how to put in the code and code lines. I tried using SET IDENTITY_INSERT Clients ON But than I get all errors bout things not being declared, set not being supported anymore, things like that. Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
View Replies !
View Related
Problem To Insert A Key Field In Another Table
hi everybody I have this case which I couldn't solve and need badly your help table1 id1 field1 field2 1 a b 2 a c table2 id1 field1 field2 a b a c I need to update table2 on the (id1) if the value of field1 and field2 in table 1 is equal to field1 and field2 in table 2 and inserting null when there is no match::: I'm trying with this code but couldn't get thru UPDATE table2 SET id1= COALESCE(table2.id1, 0) from table1 left outer Join table2 ON table1.id1=table2.id1 thanks
View Replies !
View Related
Select The Insert Value Into Field In Another Table!!!!!!
i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop SELECT Userid, Building +'/'+Room AS mailstop FROM tbl_Location i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table. so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.
View Replies !
View Related
Insert Data Into Table (one Field Is A PK)
hey all I am new to this side of the sql and just testing some statements what I want to do insert one line into a table that already has data in it KBCopy = Database name KBStatusID = (PK, tinyint, not null) KBStatusName = (varchar(100) not null) the following is what I have tried. use KBCopy go insert into KBStatus ("KBStatusID", "KBStatusName") Values ("4", "Archived") the error I receive Msg 128, Level 15, State 1, Line 2 The name "4" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. I presume this is to do with the ID? thanks jewel
View Replies !
View Related
Insert GUID Into SQL Mobile Database Table Field
Hi, I have created a database in the SQL Server 2005 and the same database in sql mobile. I have a field, which has a GUID and uniqueidentifier, when i tried to insert the same data which is there in sql server 2005 database into sql mobile database it is giving me error as below Major Error 0x80040E14, Minor Error 25501 > INSERT INTO TypeValue (TypeValue_ID,DisplayValue,TypeName,OrderIndex,IsActive) VALUES (b6483fe1-1118-4ba1-9106-0069715a75c0,Asian,ETHNICITY,1,TRUE) There was an error parsing the query. [ Token line number = 2,Token line offset = 24,Token in error = ba1 ] Please suggest me any solution. Thank you, Prashant
View Replies !
View Related
How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values. For example, to explain things in detail: create table dbo.phone_details (country varchar(20), state varchar(30), list_of_toll_free_numbers text) insert into dbo,phone_details values ( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' ) The final output I desire is: country state list_of_toll_free_numbers ---------- ------- ----------------------------------------- USA CA 8009877654, 8665764398, 8776543219
View Replies !
View Related
Skip Field Terminator While Inserting Data To A Table-Bulk Insert
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11|| While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert. BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''') Can any one help on this. Thanks, -Badri
View Replies !
View Related
How To Change Integer Field To Identity Field.
I have an integer column(not null) in a table. how can i change that to identity column through query analyzer, through a script, b'cos i have to apply this to number of databases. I tried this, but got an error: ALTER TABLE table_name alter column company_id int IDENTITY ERROR: Incorrect syntax near the keyword 'IDENTITY'. any help is welcome.
View Replies !
View Related
Change In Pk Field Into An Identity Field - How?
Ok, simply problem. I have a table that has an in primary key field. I want to (need to) set this up as identity, through a script. Is it right that there is no (working) alter table statement for this? After trying for two hours I finally just started to look on how Enterprise Manager handles this. And - EM basically... ...creates the new table, ...sets IDENTITY_INSERT to ON, ...copies the data over (select *, insert) ...deletes the old table and renames the new one. Frankly, this is not really nice. Anyone a better idea?
View Replies !
View Related
Change Field To Identity Field
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out. How can I set this field with the IDENTITY properties? Thanks
View Replies !
View Related
TSQL - Copy Table As New Table And Get The Sum Of Specific Field Group By Other Field
Hi guys, I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once. The desired result should by something like: Stock.REFERENCE JurnalTransMoves.SUF SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE 5752 10 60 5752 20 5752 30 5753 400 3000 5753 500 5753 600 5753 700 5753 800 5754 7 15 5754 8 Is there any chance to do that? Thanks in advance, Aldo. Code Snippet SELECT Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME', Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY', Accounts.FILTER AS 'ACCOUNTS.FILTER', Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP', AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME', Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode', CreditTerms.DETAILS AS 'CreditTerms.DETAILS' CreditTerms.CURRENF AS 'CreditTerms.CURRENF' CreditTerms.MONTH AS 'CreditTerms.MONTH', CreditTerms.DAYS AS 'CreditTerms.DAYS', CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC', CreditTerms.TEMF AS 'CreditTerms.TEMF', CASE WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x' WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y' ELSE 'Undefined' END AS 'VAT', Stock.DOCUMENTID AS 'Stock.DOCUMENTID', DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME', CASE WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE ELSE '' END AS 'Invoice No', Stock.VALUEDATE AS 'Stock.VALUEDATE', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.REF2 AS 'JurnalTrans.REF2', JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF', JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID' FROM JURNALTRANSMOVES AS JurnalTransMoves_1 INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY LEFT OUTER JOIN ITEMS AS Items INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID WHERE Accounts.SORTGROUP Between '3001' And '3020' AND Accounts.ACCOUNTKEY IN ('123456') ORDER BY Accounts.ACCOUNTKEY
View Replies !
View Related
Sql Identity Field
Hello friends, I had created a web application and uploaded the application. Now the problem is with the DB. I had created the DB on the server (using script). But the fields that have identity field yes is not been set Now how I can set the fields identity field to yes. Fields are already there. Only I want to set there identity field to yes.Let me know how this can be done. Thanks & RegardsGirish Nehte
View Replies !
View Related
Re-set Identity Field
Hi: I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that? Can you assist? Thanks
View Replies !
View Related
&<&<identity Field&>&>
hi friends,i have an identity column in my table.(e.x : id )and it's identity increment is 1.when i insert a row the id field is 1, and in next record the field is 2.....now , i delete second record(id=2)and now when i insert a record again , the id column is 3.i want to record be 2 instead 3.plz help me.thanks
View Replies !
View Related
Identity Field
Please, How can I get the value of the identity field of the register that I was including in the data base. I am using a stored procedure in SQLSERVER in a asp .net application and I need to show that for the user, it´s like the number of the reclamation.
View Replies !
View Related
Identity Field
Folks I am inserting some values into a table with the following stmt Insert into table(number,name) values ('12','name') In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value of the ID (the most recent one) in the next select statement. ie Select @@identity from table (any condition????) How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert. Thanks for the help,
View Replies !
View Related
Identity Field
Greetings ! Having an unusual problem - have created several (20) tables in a database. All of these tables have an identity field in them (defined as FIELDNAME int IDENTITY(1,1) NOT NULL ). In most of the tables this works as expected, but in 2, so far, when I do the first load (an insert based on a different database) the identity fields are all zero. Have recreated the tables, changed the field name and location, etc. Anyone have any clues ? TIA Eric
View Replies !
View Related
Identity Field
Dear Fellow Administrators, We are experiencing an identity problem on a table with 3.7 millions rows of data in the table. The identity field is not auto numbering any more and are wondering if anyone has a suggestion on how to get the auto number field working again without doing a bcp and restoring the table back to the database from bcp. Any suggestions? Thanks in Advance, Daimon
View Replies !
View Related
Bcp With IDENTITY Field
If my table has an IDENTITY field, say, the table schema is: CREATE TABLE BBB( id int NOT NULL IDENTITY(1,1), name varchar(20), job varchar(40)) My data file, which does not carry the IDENTITY field and its field terminator. The data file looks like this: debbie cao,programmer John Doe,engineer Mary Smith,consultant I tried to use a format file to bulk copy data from the data file to the table. Never had any luck. On the other hand, if I put a comma before the name field, say, the data file looks like the following: ,debbie cao,programmer ,John Doe,engineer ,Mary Smith,consultant then, bcp works fine. But, my data file is automatically generated. It does not suppose to have the leading comma. Without the leading comma, I have no idea how may I make bcp work. The SQLBOL says it can be done. Does anybody have an example to show me? Please help, thanks!
View Replies !
View Related
Identity Field Fix
I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx
View Replies !
View Related
Identity Field
Hello... New to SQL and have the following situation.... Example of table (currently) PayID (Primary Key) PaymentNumber (Identity Field) John 1 Tim 2 Chris 3 John (same as John above) 4 Jack 5 Steve 6 Would like to see it work this way PayID (Primary Key) PaymentNumber (Identity Field) John 1 John (same as John above) 2 Tim 1 Chris 1 Jack 1 Steve 1 Can the identity column be setup to start the counter over depending on value of PayeeID...If another Jack was added then the Payment Number would be 2 for Jack since there is already a record with value 1.... Hope this makes sense... Thanks
View Replies !
View Related
|
TRACKER
