Post reply

Import a column from one table to another

  • May 25, 2010 at 2:06 am

    #221447

    I want to import personal_id from a personel table into another empty table and have tried in many different ways for example

    INSERT INTO kurs.utbetalning (personal_id)

    SELECT personal_id FROM kurs.personal

    GO

    and I get the following error message:

    Msg 515, Level 16, State 2, Line 2

    Cannot insert the value NULL into column 'loneperiod', table 'kursdatabas.kurs.utbetalning'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    I just want to insert personal_id from the column personal_id in another table. There is now null-values. The number serie is 1-39. The column loneperiod in kurs.utbetalning shall still be empty. It will later be filled later on.

    What do I do wrong?

    Gunilla

  • May 25, 2010 at 2:21 am

    #1171915

    This wil help you out!

    Select personal_id

    INTO <Some_Table_Name>

    FROM <Existing_Table>

  • May 25, 2010 at 2:37 am

    #1171924

    Thank you.

    Select personal_id

    INTO kurs.utbetalning

    FROM kurs.personal

    GO

    gives error: Msg 2714, Level 16, State 6, Line 2

    There is already an object named 'utbetalning' in the database.

    The name of the table to import to is kurs.utbetalning. It has for now some columns but no data. The first column is utbetalning_id, the primary key with auto-increment, the second column is personal_id which I want to import from the table kurs.personal. I don´t understand the error message.

  • May 25, 2010 at 3:11 am

    #1171940

    INSERT INTO kurs.utbetalning (personal_id)

    SELECT personal_id FROM kurs.personal

    GO

    and I get the following error message:

    Msg 515, Level 16, State 2, Line 2

    Cannot insert the value NULL into column 'loneperiod', table 'kursdatabas.kurs.utbetalning'; column does not allow nulls. INSERT fails.

    The statement has been terminated

    Please have a look at the "bold" words; this behaviour cannot be possible. I guess u executed a wrong query, can you please re-affirm that u executed excatly those?

  • May 25, 2010 at 3:23 am

    #1171946

    I have tried lots of scripts for example:

    Select personal_id

    INTO kurs.utbetalning

    FROM kurs.personal

    GO

    INSERT INTO kurs.utbetalning

    SELECT personal_id FROM kurs.personal

    GO

    INSERT INTO kurs.utbetalning (personal_id)

    SELECT personal_id FROM kurs.personal

    GO

    They all give error messages.

  • May 25, 2010 at 3:28 am

    #1171949

    The destination table is created by this code:

    USE kursdatabas

    GO

    CREATE TABLE kurs.utbetalning

    (utbetalning_id INT IDENTITY(1,1),

    personal_id INT NULL,

    grundlonNUMERIC(10,2)NOT NULL,

    bonus NUMERIC(10,2)NOT NULL,

    totallon NUMERIC(10,2)NOT NULL,

    loneperiod CHAR(6) NOT NULL)

    GO

    ALTER TABLE kurs.utbetalning

    ADD CONSTRAINT pk_utbetalning PRIMARY KEY CLUSTERED (utbetalning_id)

    GO

    ALTER TABLE kurs.utbetalning

    ADD CONSTRAINT fk_utbetalningtopersonal FOREIGN KEY (personal_id)

    REFERENCES kurs.personal (personal_id)

    GO

  • May 25, 2010 at 4:44 am

    #1171983

    Thank you, now I understand better why it doesn´t work.

    Gunilla

  • May 25, 2010 at 7:55 am

    #1172083

    more problems ...

    I have six tables, each has a primary key and foreignkeys.

    One table is (lonekat) with a primary key lonekat_id and the column manadslon filled with data and a table (utbetalning) with a primary key (utbetalning_id) and the columns personal_id (foreign key), grundlon, bonus, totallon (NOT NULL) which all are empty.

    These two tables are connected to each other via the table personal and personal_id, since personal_id is connected to lonekat_id in the lonekat table.

    Now I shall copy data from manadslon in the lonekat table to grundlon in the utbetalning table where I already has imported personal_id from the personal table.

    The other columns in the utbetalning table will later be filled with calulated data from other tables. However just now these columns are empty.

    I have tried to split the process in small pieces but all my trials have end up in different error messages. I really don´t know what is the easiest way to attack the problem.

    Here is one of my trials:

    INSERT INTO [kursdatabas].[kurs].[utbetalning]

    ([personal_id]

    ,[grundlon]

    ,[bonus]

    ,[totallon])

    VALUES

    ('kurs.personal.personal_id, int',

    'kurs.lonekat.manadslon, numeric(10,2)',

    'kurs.produkt.provision * kurs.lonekat.provisionsandel numeric(10,2)',

    'kurs.utbetalning.grundlon + kurs.utbetalning.bonus numeric(10,2)')

    GO

    which gives the error: Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value 'kurs.personal.personal_id, int' to data type int.

    I don´t understand the error, personal_id in kurs.personal is not a varchar, it is int

    I think it has to be KEEPIDENTITY somewhere in the code.

    Can you give me some concrete tips?

    Gunilla

Viewing 8 posts - 1 through 8 (of 8 total)

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