Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Unanswered: Proper syntax for bulk insert?

    Hi,

    I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.

    How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?

    Here is what I'm doing so far:

    CREATE Procedure Insert_From_Personnel
    @emp_num char(10),
    @Frst_Name char(10),
    @Last_Name char(10),
    @DivisionID char (4)

    as

    INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

    VALUES (@emp_num,@Frst_Name,@Last_Name,@DivisionID)
    GO

    Thanks for any help,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * INTO myDB..Table FROM oneDB..Table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    7
    Thanks Brett,

    Now it's giving me an error that says 'object Individual is already in the database.'

    Have I placed the select * in the right place?

    CREATE Procedure Insert_From_Personnel
    @emp_num char(10),
    @Frst_Name char(10),
    @Last_Name char(10),
    @DivisionID char (4)

    as

    select * into individual from FROMGDPersonnelByDivision

    INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

    VALUES (@emp_num,@Frst_Name,@Last_Name,@DivisionID)
    GO

    Thanks,

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    insert into db1..Individual (fields)
    select fields
    from db2..Individual
    This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    Code:
    insert into db1..Individual (fields)
    select fields
    from db2..Individual
    This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?

    fields....oye....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, OK. I am being lazy. I admit it. ;-)

    Still, it is slightly better than

    insert into table
    select *
    from other_table

  8. #8
    Join Date
    Sep 2004
    Posts
    7
    Thanks guys, I DO need all the help I can get...


    MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.

    At this point, I don't mind slow, I just want to see it work. So now I've got:

    CREATE Procedure Insert_From_Personnel

    as

    INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
    select emp_num,frst_name,last_name,[division id]
    from FROMGDPersonnelByDivision

    GO

    I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?

    Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.

    One last thing... how can I say if it DOESN'T exist INSERT in the SP?

    Thanks for your help!

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if not exists (select...)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Sep 2004
    Posts
    7
    I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision

    What the heck am I missing?!

    CREATE Procedure Insert_From_Personnel
    as
    if not exists (select emp_num,frst_name,last_name,[division id])

    INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

    from FROMGDPersonnelByDivision
    GO

    Can I have one more hint, please?

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, I agree, the combination is finite and you're close

    CREATE Procedure Insert_From_Personnel
    as
    if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
    else
    print 'Record already exists'
    return (0)
    GO
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Sep 2004
    Posts
    7
    rdjabarov,

    Help me out here. Why isn't this working?

    When I use your example (simplified):
    CREATE Procedure Insert_From_Personnel

    as

    if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
    INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

    GO

    It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.

    After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.

    Thanks for any help!

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.

    -PatP

  14. #14
    Join Date
    Sep 2004
    Posts
    7
    Hmmmm... OK,

    The select is there. It makes sense in english;

    if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table

    >> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)

    Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?

    Thanks,

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    INSERT into individual (
       IndividualID, FirstName, LastName
    ,  DivisionID
       ) SELECT
       emp_num, frst_name, last_name
    ,  [division id]
       FROM FROMGDPersonnelByDivision
       WHERE NOT EXSISTS (SELECT *
          FROM individual AS b
          WHERE  b.InidividualID = FROMGDPersonnelByDivision.emp_num)
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •