Post reply

Simple Insert

  • July 6, 2005 at 7:43 pm

    #90141

    Hello to the best sql fourm on the net

    I need a script that will allow me to insert into one column and add an FK at the same time.

    Here is my problem, i have a product table and this product has products in it that have alot of the same values as each other.

    Here is how my tables are set up. Product, ProdcutAttribute,  Attribute, AttributeCategory

    The ProductAttribute table only has two columns in it and this is the one that i would like to know what an inset statement would look like so that i may insert all this data at one time.

    Basically i am wanting a insert that will insert the productID (Of certain products) and the AttributID (Of certain values) to insert into the ProductAttribute table.

     

    Thank you for your help ahead of time and if i am unclear.. Please by all means tell me that you need more info.

    (In other words i could have a insert statement that could be resused over with the change of the productID and - or + some attributes)

    Dam again!

  • July 6, 2005 at 7:46 pm

    #571616

    I forgot something, here is a link to where i have a pic of my tables and the one on the far right is the one in question. IF you;ll notice that the left column is where the productID will go and on the right the attributeid.

     

    http://afcc1.com/sqlHELP/sqlMajor.jpg 

    Dam again!

  • July 7, 2005 at 12:59 am

    #571654

    The execution of this procedure should follow your new Product addition.

    create procedure p_copyproduct

    (@newProductId int, @prevProductId int)

    as

    begin

    Insert ProductAttribute (ProductId , AttributeId)

    select @newProductId , AttributeId

    from ProductAttribute

    where ProductId = @prevProductId

    end

    go

    So the new product has the same attributes of a reference (or previous) product.

  • July 7, 2005 at 6:58 am

    #571740

    Thank you very much...

     

    Erik...

    Dam again!

  • July 7, 2005 at 7:10 am

    #571742

    The only part i am not understanding is the @newProductID. I realize that it is a input parameter, i am not getting how to link it to a column.

    Erik...

     

     

    Dam again!

  • July 7, 2005 at 7:19 am

    #571753

    Nevermind I see how to run this.

     

    Dam again!

  • July 7, 2005 at 7:25 am

    #571759

    Happy to have waited to be able to help you .

  • July 7, 2005 at 7:29 am

    #571764

    Im not worthy!

    Like Arnold ,.  (I'll be back)

    Dam again!

  • July 7, 2005 at 7:49 am

    #571777

    I know .

  • July 7, 2005 at 9:36 am

    #571903

    I have a Prodcut table that has a FK (ProductID) in a ProdcutRestrictions table. This ProdcutRestrictions Table need to have a uniquekey going to another table called (LadderSpacing)

    How do i connect these two tables correctly?

    Right now i have a LadderSpacingID int,4 (Column)in the ProductRestrictions table and the LadderSpacing table has a PK_LadderSpacing (Column); however i am gettin an error message that the constraints are not matching up. That is probley due to the fact that i have yet to do a operation like this; however, i have got to split this information up it is just to detailed for one table and things would get out of hand if i tried to bunch all of this data into one table.

    Thanks,

    Erik..

    Dam again!

  • July 7, 2005 at 9:40 am

    #571905

    You really need to get a book on design, those are simple questions you should be able to answer yourself.

    If there's only one LadderSpacing per product, then a Foreign key is correct. If you can have more than one then you need another table with FkProductRestriction and FkLadderSpacing.

  • July 7, 2005 at 9:49 am

    #571915

    Do you have a good recomendation for a relational database book? and or videos?

     

    Dam again!

  • July 7, 2005 at 10:02 am

    #571931

    There are a few suggestions in there :

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=122280

    You might wanna start a new thread for this one.

  • July 8, 2005 at 9:47 am

    #572451

    I have just gotten to a point this morning that i am able to use this stored procedure that i received from you the other day. I am receiving an error message that :::

    Running dbo."p_copyproduct" ( @newProductId = 11, @prevProductId = 15 ).

    Violation of PRIMARY KEY constraint 'PK_ProductAttribute'. Cannot insert duplicate key in object 'ProductAttribute'. :::

    I entered some data the other day that this error is referring to, My question to you is, (So that i will know in the future) is there a SP that will over write the PK  that exist now and reinsert them along with the new data?, and (or) would it be better for me to put a grouping  column in this table so that i can group this information and pull every thing around the PK_ that already exist? (I am asking this for a future development standpoint so that i will know next time

    Thanks,

    Erik...

    Dam again!

  • July 8, 2005 at 10:14 am

    #572482

    I didn't provide that solution. Looks to me like you reinsert data that is already in the table. So I would just add a where not exists in the insert statement so that you avoid duplicates.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply