Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-09-30 : 08:16:41
|
| Michael writes "I have defined a table XYZ that has 80 columns of various datatype. All of the programming should be done in VB & ADODB.I want to select a row from this table and insert a new row that exactly like the selected row except for the Pimary Key and a couple of other columns. One of the quicker techniques I just learned from the group was to create a temporary table that is exactly like XYZ, except for the PK column; select the row I want from XYZ and insert it into this temp table; make the updates I need; then select from this temp table and insert it back to XYZ.The issue with this is I have to create the temp table dynamically with all of the right data types for the columns. I can get to "some" info on the data type of the columns from the XYZ table by using ADO's RecordSet and ADO's Fields objects, but the kind of info it stores is not easily translates into strings that you can just simply string them together to create an "CREATE TABLE" statement. It seems the only way to do that that I know of is to create a map between what are provided by ADO and the real Data Types, and that means I have to know the right mapping.Questions:1) is there an easier way to create the above temporary table?2) my real need is: an easy way to create the record as described above.Thanks in advance,Michael" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-30 : 08:25:16
|
| suppose you want to copy the row with a PK of "123", add 10 to the "value" column, and change the status to "Updated", and put in the current datetime.here's how to select that:SELECT Value + 10, 'Updated' as Status, getdate() as CurrDateTime, col1, col2, col3, (... other columns)FROM tblWHERE ID = 123so, using that, just insert it into the table:INSERT INTO tbl (Value, Status, CurrDateTime, col1, col2, ....)SELECT Value +10, 'Updated', getdate(), col1, col2, ...FROM tblWHERE ID = 123voila. no temp tables, no "data type" issues, you have copied a row from the table, altered it, and put it right back in.(in the above example, ID is an IDENTITY. if the PK is not an identity, you may need to do some work to generate a new PK for the new row and insert it into the row's PK columns)- Jeff |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-30 : 08:56:12
|
| Important note: if you had thought of doing this 1 row at a time (take out, copy, process, put back) note that you can do this for a SET OF ROWS all at ONCE simply by altering the WHERE clause in the query I gave. This is usually about 1,000,000,000 times faster (that's an estimate).- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-30 : 09:22:05
|
| If you have a sql server based system it's always best (easier to develop, maintain, diagnose and more efficient) to make all database access via stored procedures.Then your problem becomes trivial. Pass to the SP the PK of the row you want to copy, the new PK and the changed values and do the insert in the SP. Then you don't have to worry about changes to the table structure because the SP will just be changed at the same time - if there is no change to the client interface then the application isn't affected by the change.>> One of the quicker techniques I just learned from the group Which group? Doesn't sound like there is much database expertise here.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Creating a temporary table using existing column definitions