Solved

importing data to sql 2008, identity column

Posted on 2011-09-10
4
352 Views
Last Modified: 2012-05-12
I have a set of non related tables in sql server.

Product Table
columns: productID, product bar code, description
it is populated with values:
1        wewe444           box
300   wewe889            pen
301   weweppo            pencil
(productId's are unique in this table)

I have created a relational database with the same columns for product table. I set productID to primarykey and identity column.

am I going to have problem when I import data to this new table?..how productID's are going to be imported to the identity column?

thanks in advance
0
Comment
Question by:shmz
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36516247
to insert rows into a table with identity field, giving a value for that field instead of letting the identity assign one automatically, you need to use this:
http://msdn.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx
0
 

Author Comment

by:shmz
ID: 36516305
Hi Angelll,

Thanks for the article. This means when I do a bulk import for the first time into an empty destination table, it should not cause any problem, given that the source table has unique productIds. Right?

Also after bulk import is done, when I start to add individual new items to destination table, the database automatically recognizes what the new productID is, regardless of what stored in the last row of the table (as it may not be in correct order)?

Thanks
0
 

Author Comment

by:shmz
ID: 36516476
Angelll, Thanks for the information. I just tested the database, and the sql does find what is the highest identity value in the column and then set the new record accordingly.

Thanks
0
 

Author Closing Comment

by:shmz
ID: 36516478
Many Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to import result to CVS from sql server management studio without comma 2 36
SQL DATEADD 10 78
Sql query with where clause 2 45
Substring works but need to tweak it 14 31
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question