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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Copying records for Testing

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 table
2) 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 temporary
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.

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
Go to Top of Page

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 work

select * into DB2.<test_tableB>
from dbo.<tableB> a
JOIN dbo.tableB b on a.cust-id = b.cust-id

Can 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!
Go to Top of Page
   

- Advertisement -