Insert

  • you keep dancing around the issue i think; does the foreign key in table two already exist? i think not. does the data exist in another table and you are trying to change this from a flat table to a one-to-many relationship?

    i looked at the rough schema you provided, and i did not see any implied relationship between the two tables. no columns named same/similarly, etc.

    if there was an implied relationship, then you could infer the FK value based on that relationship, but i don't think you are giving enough einformation.

    forget asking "theoretical" questions. use the actual information.

    show the actual schema of the tables.

    show the first handful few rows of the data in the tables as they are now.

    with that, someone might identify the relationship.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • How can provide data for u, can I attach any files in this forum?

    Anyways Let me explain u clearly..there are no matching records in both the tables. my assumption regarding the relationship is just to retreive data from both the tables together in my APP and so I added a FK column in table2.

    Do I need to have a similar column other than FK in table2 which match with a column in table1.

  • Check this out :

    Generate insert statements

    Also what do you need to present on that form??

  • Ok let me give another example for INSERT query

    Table1[MeasureNO(PK),subsection,code,owner]

    Table2[Type,source,MeasureNo(FK),owner]

    Here in Table2 MeasureNo has multiple owner. How can I insert owner column in table1 from table2 where  MeasureNo has relation in both tables.

  • Let me see if understand this.  Table 1 (which is the Primary table) has no record in it.  Table 2 (which is the Foreign Key table) has a record in it.  For some unknow reason, either the record in Table 1 was deleted without cascading or someone manually entered a value in Table 2, (whatever the cause...). 

    If MeasureNO (PK) in Table 1 is an IDENTITY field, you will need to SET IDENTITY_INSERT ON to put Parent (Table 1) values from the Child table (Table 2) into the Parent table (Table 1).  Be sure to SET IDENTITY_INSERT OFF when you TRANSACTION is completed. 

    I would also recommend you get a count of how many of these records need this action.  I would SET NOCOUNT OFF and wrap your tranaction to make sure the counts match. 

    SET NOCOUNT ON

    BEGIN TRANSACATION InsertIntoTable1

         SET IDENTITY_INSERT Table1 OFF

         Do your insert

         SET IDENTITY_INSERT Table1 ONN

    ROLLBACK TRANSACTION InsertIntoTable1

     

    Once your numbers match, you can COMMIT TRANSACTIONS InsertIntoTable1 

    (Theoretical questions are often VERY difficult to answer, especially when you will not give all the information requested..., numerous times....). 

    I wasn't born stupid - I had to study.

  • ok much better. you still should have provided real table names instead of examples.

    since you are inserting into two tables at run time, you'll want to do this with a stored procedure:

    CREATE PROC PR_INSERT_TABLE1 ( @subsection int,@code varchar(3),@owner int,@type int,@source int)

    AS

      BEGIN

        DECLARE @MeasureNO INT

        INSERT INTO Table1[MeasureNO,subsection,code,owner) values (@MeasureNO,@subsection,@code,@owner)

        --get the newly created primary key

        SELECT @MeasureNO =scope_identity()

        --sql 7 has no scope_identity, use @@identity instead, be aware on high volume db's this is prone to mistakes, hence the better function in SQL2000

        INSERT INTO Table2 (MeasureNo,Type,source,,owner) values (@MeasureNo,@Type,@source,@owner)

      END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • OK I think I have to give REAL table. sorry for the inconvinence.

    Table PM

    MeasureNum  int 4 (PK)Identity

    PMOrderBy    int 4  

    Type           char 1 

    Measure       nvarchar 255 

    Frequency    nvarchar 255

    MeasuredBy  nvarchar 255

    Goal            nvarchar 255

    Source        nvarchar 255

    Definition     ntext 16

    Formula       nvarchar 255

    Table Owners

    ComptCode  nvarchar 50

    MeasureNum int(FK)

    Owner         nvarchar 50

    MeasureNum is the relation between tables.

    I have inserted data into PM from a table of different database. Now I have to insert data into Owners from a table of different database, but cud not do tht since I have relation.

     

     

  • Stupid question here... what programming/database design formation do you have?

  • in the "other" database which was your original source for the [PM] data, is the information all together is a larger table?

    can you post the schema of the source table?

    what was the original SELECT statement that pulled the data from the "other" database  into [PM]

    the WHERE statement might tell us what relationships you are not revealing here. 

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This is the insert statement pulling data from other DB

    INSERT INTO perfmeasure..PM

    (pmorderby,type,measure,frequency,measuredby,goal,source,definition,formula)

    SELECT

    perfsection,subsection,pmorderby,type,measure,frequency,measureby,goal,source,definition,formula

    FROM PM..boa_pm

     

    May be very silly..but I am damn new to SQL, learning things here.

  • Do U mean tht the Design is not at all OK.

    What changes u think have to be made in this scenario.

  • I'm not trying to insult you in any, but if you tell me you have 0 experience, you need to buy a course or follow a course... this site is for help, not for teaching everything from A to Z.

    A few comments on the design :

    Why are you using nvarchars instead of any number format suitable for the information?? what are they all set at 255?

  • Yeah u r right but the thing is I migrated data from Access into sql database,when i tried changing data type then it says data may be lost.

    What can be done in such cases

  • You can make sure no data loss is possible :

    Declare @demo table (name nvarchar(25) not null primary key clustered)

    insert into @demo (name) values (N'this is a long string')

    insert into @demo (name) values (N'a short one')

    --check if data would be loss if converted to varchar(20)?

    Select len(name), name from @demo where name cast(name as varchar(20))

  • FYI..

    I am doing a job where I have to migrate old databases from Access and Oracle to SQL Server 2000 and then redesign them in a normalized way,later import all the data from those databases into my new design.

Viewing 15 posts - 16 through 30 (of 34 total)

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