September 19, 2008 at 12:22 pm
I have setup an IDENTITY column in my table, which no data is stored in at this time. However, I need to update the table with records from our manual operation, (spreadsheet). How do I go about this? Do I just set the seed to correspond to the first record in my spreadsheet? What issues will I run in to? Any help will be greatly appreciated.
September 19, 2008 at 3:34 pm
You'll want to load the spreadsheet into a staging table and load it into the table with the identity value from there.
Does the spreadsheet have values that go into the table's identity column? If so, you need to set identity insert on the table before inserting. (ie, SET Identity_Insert TableName ON). If not, you just don't insert into the identity column.
Regardless, your insert query has to include every column declared explicitly in the statement when an identity column is present.
You'll also want to turn the identity insert statement off again after the table is loaded.
Hope that helps.
September 19, 2008 at 4:38 pm
Bpowers:
Do I just set the seed to correspond to the first record in my spreadsheet?
Identity values are normally automatically incremented when a row is added to a table. Why would you want the identity value to correspond to the row in the spread sheet?. If there is no requirement that it must correspond to the row number of the spread sheet, just import the data from the spread sheet into the corresponding columns in your table, with the exception of the identity column which will be incremented automatically.
September 22, 2008 at 5:35 am
i really dont understand ur need actually ....u have one column with identity propertiy in sql table and u want to update that table which actually doesn't contain any row...if i m right
then....buddy ...you cant update any table which has no row.:)
first of all give detailed view of ur problem .and also schema of ur table and what actually u want to update ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2008 at 7:11 am
The IDENTITY column is our NonConforming Material Number. This is a unique number manually assigned when the reports are setup. Moving forward we will be using an Access database and form to enter this info, and I wanted the NMR_No to be automatically populated. However, I have to get the past data loaded in as well, from an excel spreadsheet the users have been using for years.
September 22, 2008 at 7:42 am
It won't do any harm to have an identity column in your table, one which isn't populated with data from your spreadsheet but which is automatically assigned a value as a row is inserted. The most important and obvious reason for this is that it's a dependable row address for deletion or updates.
You are quite likely to find duplicate NMR numbers in your data as you collect spreadsheets from different departments - what happens when you find one? What happens when you get two NMR numbers on the same spreadsheet and they've been prefixed or suffixed with a letter to distinguish between them?
Reference numbers like these often have a meaning to the business, and the business often abuse the rules regarding their integrity. Identity columns have a meaning explicitly for the database, as row references, and the rules regarding their integrity are abused at your peril.
If you can be totally, absolutely, 100% sure right now that there will be no duplicates or other exceptions to the NMR number in the data you're importing, then it might make sense to use the identity column for the NMR numbers.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 22, 2008 at 9:24 am
Or, have an NMR Number, plus an Identity column.
From moving over to the new system, NMR = the Identity column for all new values with the Identity column providing the referential integrity.
The user uses NMR Number and doesn't even need to know that the identity column exists - which also gives you more flexibility in 6 months time when someone pops up with, 'now you know the NMR Number ... we've just decided it'd be better if we changed it so that .... '
September 22, 2008 at 9:29 am
Andrew Gothard (9/22/2008)
Or, have an NMR Number, plus an Identity column.From moving over to the new system, NMR = the Identity column for all new values with the Identity column providing the referential integrity.
The user uses NMR Number and doesn't even need to know that the identity column exists - which also gives you more flexibility in 6 months time when someone pops up with, 'now you know the NMR Number ... we've just decided it'd be better if we changed it so that .... '
Exactly - thanks Andrew 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 22, 2008 at 9:48 am
If you already have the SQL Server table setup with an IDENTITY column, and the non-conforming material number from the spreadsheet is just an integer value that you want to load along with the other column, you might want to read in Books Online about the SET IDENTITY_INSERT statement. This will allow the existing records to be loaded with the NMR_No you already had. Just make sure you do a SET IDENTITY_INSERT [tablename] OFF after you're done loading the data.
September 22, 2008 at 9:53 am
Got it. I appreciate all the help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply