Move data from one table and put the data in another

[ralphtrent]

Hi
I have a table that can get very large very quick. I want to create an "archiving" procedure. The database in on SQL 2000. My origional plan was to bcp the data out and the bcp the data in, but i am getting an error when I do that. It says that 'the file specified can not be found', but that is wronge because if I echo out my command that I am using to the BCP out and run that in a command window, it works, so I am not sure what the error is really saying.

So my alternate way of doing this is to select all the data and put it in to a Data Table and then write the data to a file tabbed delimated. Now I want to get the data in to the archive table on the datasource. I was thinking about writting an insert statemenent for each line in the file, but I will not know which datatype the data is, so I will not know when I need quotes and not need them.

So my question is, if I have a datatable filled with data, is there a way to put that data in another table on the datasource?

Thanks

 

[chiph]

Why not use a SELECT with your INSERT statement? That way it all happens on the server.

See the example at the bottom of:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ralphtrent]

Because I already have the table I want to save the data to. With Select Into the table must not exist.

What I have tried since posting this was create two datasets, one with the archived data, and another with the new data to be archived. I merged the new data into the archived data, and then I did a [SqlDataAdapter].Update, but I get an error message

Line 1: Incorrect syntax near '?'

I think that error is coming from this line

objDa.InsertCommand = new SqlCommand("INSERT INTO ONYX_SQL_DATA_ARCHIVE VALUES (?,?,?,?,?,?,?,?)",objConn);

I am trying to create a generic insert statement and I found this on the web some where.

Any ideas on how to do something like this?

 

[Coolstep]

Dear Ralphtrent,
To have a generic insert statement, in my opinion u'll need to set the table name and values part as variable strings, like:

string tableName="employee"
string valuesPart="1,'John',null,2750.78";

sqlInsert = "insert into "+tableName+" values ("+valuesPart+")";

I wish it helps;

 

[davidwendelken]

In Oracle, you would do something like this:

insert into new_table
(col_01
,col_02
)
select xcol_01, xcol_02
from old_table

The new_table doesn't have to be empty.

There is even a merge command, which will do an insert or update, as appropriate.

Don't know yet whether those features are available in sql server.

David Wendelken

http://www.casetech.net

 

[Coolstep]

I had a similar task in my current project and i prefered haved a stored procedure, faster and more reusable in my opinion.

 

[davidwendelken]

Absolutely!

It's crazy to move data from one table across the network only to move it back across the network to put it in a new table!

David Wendelken

http://www.casetech.net