inserting into a table using a cursor 1

[mbcmike]

I need to insert specific records from a cursor into a table depending on whether or not that record is already in the table based off of an employee number.

I have my cursor set up, but I can't figure out how to insert from that cursor into a table. I was trying to use a normal insert with "from current of cursor_name" at the end, but that of course didn't work.

Any help is appreciated.
Thanks

 

[jbenson001]

What are you trying to do with the data exactly? Show some sample data, and desired resutls. There is probably a set based solution to your problem.

Jim

 

[mbcmike]

I have a cursor that when I run "fetch cursor_name" it returns values for the next record in line.

The table I am trying to insert to has the same number of columns and column names as the record in the cursor.
The insert I was trying to use looks as follows:

insert into table1
(
col1,
col2,
col3
)
select
col1,
col2,
col3
from cursor_name


It doesn't like the fact that I am trying to take values from a cursor...

 

[jbenson001]

Show data and what you want to do based on data. Forget the cursor for now.

 

[SQLSister]

Foreget you ever even heard the word cursor. Cursors should almost never be used and virtually never for inserting data. Cursors will kill performance on a system especially as more records are in the tables. I have rewritten cursors that took hours to execute into set-based solutions that take milleseonds or seconds or minutes.

I'm with jbenson, show us your dat and what results you want and we'll give you a better solution than a cursor.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mbcmike]

Ok, I have 4 tables (table1,table2,table3,table4)

Each table is from a different health insurance company that has information on employees where I work. One employee may be in multiple tables. However, the tables contain different information on employees. For example, one table may have the employee address where as the next table may not, but it does have the employee age. The information that all 4 tables have in common is employee last name, first name, and birthday. I want to insert this information into one table, but as I do it I want to make sure that I am not inserting an employee twice.

My process is to be as folllows: If employee exists in main_table, then update existing information, else insert it. This way I should get information from all tables on each employee but I will not have duplicate employees.

If you are asking for my code, I really don't have any if you're telling me to ignore the cursor. That was what I had...I wanted to use a cursor to go through a select statement that took from all 4 tables and inserted one by one into the main table.

Since you're telling me not to use a cursor, I'm not looking for someone to paste a bunch of code in here so I can just copy and paste, but if you have a quick suggestion on another way to do what I am trying to accomplish I am all ears.
Thanks

 

[vongrunt]

> My process is to be as folllows: If employee exists in main_table, then update existing information, else insert it.

Update existing employees with inner join, then insert new employees with left outer join.
Repeat for each table.
Total = 8 trivial queries.

But... suppose different information about employee exist in two or more tables - one says age is 45, another 46. Kind of replication conflict in lack of better terms. What then? One information is obviously wrong...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mbcmike]

Ah yes I thought of that possible problem, not sure what direction to take on it yet.

I will however try your sugestion