Adding SQL records using dataset

  • Thread starter Thread starter joe klein
  • Start date Start date
J

joe klein

I need to read from a sql table all rows with '1' in userid column. I
am able to do this and put them in a dataset.
I then need to ADD to the sql table the same information in all rows,
except replace the userID column of 1 with another int value.
remember, I do not want to cahnge the value, but ADD rows.
I am using user ID as a 'template' for new users when they are added
to SQL. I want them assigned the same inforamtio as userID 1.

How is the best way of doing this?
thanks for any advice-
 
Hi Joe,

joe klein said:
I need to read from a sql table all rows with '1' in userid column. I
am able to do this and put them in a dataset.
I then need to ADD to the sql table the same information in all rows,
except replace the userID column of 1 with another int value.
remember, I do not want to cahnge the value, but ADD rows.
I am using user ID as a 'template' for new users when they are added
to SQL. I want them assigned the same inforamtio as userID 1.
Click to expand...

In short:
Create a dataadapter capable of saving the table back to database, add rows
to dataset and invoke dataadapter.Update method on DataTable in question.
 
Hi,

I would use just INSERT SQL statement without selecting any data to the
client. Your statement would look like (I did not test it, but you will see
an idea), assuming you need to replace ID=1 with ID-4

INSERT INTO MyTable SELECT 4, Field1, Field2 FROM MyTable WHERE ID = 1
 
Thanks for the input. This is what I kind of was starting to do, do I
grab the dataset first with the rows of '1'. then create anotehr
dataset changing the userid column to the actual userid number and
save back?
or I guess I need to ADD to the dataset as ADO will think I am
updating the original somehow and UPDATE not ADD.
thanks for your help.
 
Hello,

It still amazes me how many persons write this kind of SQL. Your insert
statement will work only when columns are physically in the expected order
and no-one will add new columns to the table. The correct way, IMO, is to
write the statement like this:

INSERT INTO MyTable( ID, Field1, Field2 ) SELECT 4, Field1, Field2 FROM
MyTable WHERE ID = 1

JMu
 
thanks to you both, this is exactly what I need.
One more thing- is it possible to do this without specific column
names? as i have about 10 tables I will need to do this for. Jmu,
thanks for your tip, but htese fields will not change, if they do, i
can just change the query.

Thanks again.
 
All the fields will be in a same order all the time, because we select in
insert using same table
 
Back
Top