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,SELECT * INTO myDB..Table FROM oneDB..Table|||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,|||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?|||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 ...|||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...|||OK, OK. I am being lazy. I admit it. ;-)

Still, it is slightly better than

insert into table
select *
from other_table|||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!|||if not exists (select...)|||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?|||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|||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!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.

-PatP|||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,|||How about: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|||Pat! Pat! It worked!

I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.

Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)