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 |
|
Rahul Raj
Starting Member
41 Posts |
 Posted - 2014-11-18 : 23:35:59
|
| HI,I am working on a sql server SP to delete records based on inactive customer ID.There is a table A which contains the cust-ID and the status(e.g A-active,i-inactive). My SP is fetching all the inactive cust-id records from table A and deleting the entries from other tables(around 20 tables) based on the cust-id.I am trying to do the data set-up to test this SP but have some queries:My approach is to copy all the impacted tables(big size) into another database for testing and tried implementing the below logic :1)select all(inactive customers) records from table A and store it in a temp table2) use the below sql query to copy the records insert into DB2.<tablename> where cust-id in (select * from temp table)I am getting error while creating the temporary table (SQL version 2008).1)create temporary table <tablename> - invalid keyword temporary2) create table #<tablename> - the table is getting created but is not getting deleted thereby if I run it the next time it says table already exists. This doesn't seems to be the property of temp table. Can someone please suggest as both the above syntax are not working.Also, will temporary table be a good way (performance wise) or can I use the below sysntax for all the 20 impacted tables :insert into DB2.<tablename> where cust-id in (select cust-id from table A where cust-status = 'I')will it degrade the performance . The records to be copied for testing is around few hundreds for each table.Thanks for your help! |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-11-19 : 01:48:59
|
quote:
2) create table #<tablename> - the table is getting created but is not getting deleted thereby if I run it the next time it says table already exists. This doesn't seems to be the property of temp table.
Use the syntax - DROP TABLE #<tablename> .Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Rahul Raj
Starting Member
41 Posts |
Posted - 2014-11-19 : 14:19:45
|
| Hi JackV,Thanks for the response. I have used IF exist condition to check if the temp table is available and then Drop.Just to add above query regarding the table copy approach,Can I use JOIN to copy to all the impacted tables based on the inactive cust-ID's in table A( total inactive cust-id in table A is around 150). I tried using the below syntax but it didn't workselect * into DB2.<test_tableB>from dbo.<tableB> aJOIN dbo.tableB b on a.cust-id = b.cust-idCan someone please correct the above syntax. Also is there any way I can copy all the records having inactive cust-id from table A into all the impacted tables in a single query. PLs suggest a better way to copy the records without impacting the performance as some tables have around 20K record for a cust-ID.Thanks for your help! |
|
|
|
|
|
|
Copying records for Testing