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 |
|
sharad.rk88
Starting Member
7 Posts |
 Posted - 2010-11-23 : 01:22:18
|
Hello all, I have a strange requirement here.. Here it goes..I am told to do a delete function.. for which i need to store the data into some other table first and then delete original rows... Seems easy right. But the problem is i have to perform delete operation on some 10 tables each of which has loads of data.. some with even 100 columns.. And i have to store all the data from these tables into a single table before deleting.  Is there any suggestion on how to do it? How to insert data from multiple rows in single table? Thanks in advance..--Sharad |
|
|
sreekanth939
Starting Member
12 Posts |
Posted - 2010-11-23 : 01:38:48
|
| insert into Table_Name Select 1 ; :) |
 |
|
|
sharad.rk88
Starting Member
7 Posts |
Posted - 2010-11-23 : 02:21:30
|
| what? i didn't get u... that doesn't work for me...--Sharad |
|
|
|
sreekanth939
Starting Member
12 Posts |
Posted - 2010-11-23 : 02:35:37
|
| select the rows u wanted to delete.and try this method select b.* into "New_Table_Name" from ( "ur select query with column names " ) as bselect b.* into New_table from (select 1 as b,1 as v,1 as d,1 as funion allselect 1,1,1,1) as bif it doesnt help u ignore this replay ;) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-23 : 04:06:26
|
| Optionsyou could have a single column to hold all the data from a table. Could be held as xml or similar.You could have columns in the table srctbl, col01, col02, ... them map the columns from the table to col01, col02...In both these cases you would need to convert to charcter format if possible.With these you don't have to convertSimilar to the second butsrctrbl, charcol01, charcol02, ... intcol01, intcol02, ...hold the columns from each tablesrctbl tbla_colname1, tbla_colname2, tbla_colname3, ... tblb_colname1,...The columns are populated for the table depending on the source tabale name - the others are all null.This is probably the easiest one to deal with. It is actually partitioning by table so you might thing why not have a table for each source table (lets call then audit trail tables) and combine with a view if you need to present as a single datasource.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sharad.rk88
Starting Member
7 Posts |
Posted - 2010-11-23 : 05:27:42
|
| hello webfred, Thanks for the interesting concept... I researched a bit on output clause. But i didn't find how to insert values into already created table using output... all the examples on the internet show declaring a new @table and inserting deleted records into it.. I would appreciate if u provide me an example.. Thanks--Sharad |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-23 : 05:45:34
|
| Just replace the table variable name with the table name.The output clause can take a table variable, temp table or table.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Inserting data from multiple rows into single colu