CODEWITHSUNDEEP
c#asp.netsql
Paul
Paul

Reputation: 3

Inserting row into table with auto increment value

I got a table PartsMedia where I can insert all the images related to a product . The table has the columns :

PartsMediaID  , auto-increment
PartsNo
MediaLink
MediaDescription
CatalogCode
SortCode

I want to insert a complete row with automatic increment and the PartsNo should be the same as the PartsNo from the PartsMaster table. The medialink should be the PartsNo + '-2.jpg' The mediadescription is for example 'image2' The CatalogCode should be 'catalog' and the sorting code should be '0'

From The partsMaster table I Just need the PartNo So I can add this to the PartMedia Table. The PartNo is the foreign key in the PartMedia table.

The following I got so far but no luck 

 insert into dbo.PartsMedia (PartNo,MediaLink,MediaDescription,CatalogCode, SortCode)
 values (dbo.PartsMaster.PartNo, PartsMaster.PartNo+'-2.jpg','image2', 'catalog','0')

I need some help .

Kind regards,

Upvotes: 0

Views: 3438

Answers (3)

Tim
Tim

Reputation: 5421

Your insert statement lacks a select-clause that grabs the correct row(s) from the PartsMaster table.

              insert into foo(a, b, c)
              select x, y, z from T

Upvotes: 0

Merlyn Morgan-Graham
Merlyn Morgan-Graham

Reputation: 59101

(warning: Dev pretending to know anything about databases)

It sounds like you have a data normalization problem. Each entity should have only one ID in your database, and it only makes sense for something like a surrogate key ID (for that table) to be auto-increment.

If you want to refer to the ID of an entity in a different table, you should have a foreign key constraint, and that column shouldn't be auto-increment.

Reason being - what if in the future you want more than one piece of media (image) for a part? Maybe in the future you'll want pics and vids. In these scenarios, you need to support duplicate PartsNo values.

Upvotes: 0

MartinHN
MartinHN

Reputation: 19772

It's unclear to me what you really want.

But if this is MS SQL, and you're trying to override the identity column (which as auto increment), you need to tell Sql Server that you can insert a new value in the identity column:

SET IDENTITY_INSERT tablename ON

YOUR INSERT GOES HERE

SET IDENTITY_INSERT tablename OFF

Upvotes: 1

Related Questions