Using CTAS create stored Procedure for Client

Question

Using CTAS create stored Procedure for Client

viresh29

Ten Centuries

Points: 1111
More actions
May 14, 2014 at 10:15 am

#288770

Hello All,
I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
and it will change all table names to UNITED_ACTIVITY
I want to create this SP for different client.
Any suggestions on this are really appreciated.
Thanks:-)
Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Why do you need to do that? Why not just index the ClientName/ID in the table, and then use a stored procedure to filter the data?

For example:

CREATE PROC usp_CompanyRecords

@CompanyID INT

AS

SELECT...

FROM BigTable

WHERE CompanyID = @CompanyID;

Having a ton of tables with the same structure seems like an insane amount of work. (What if you want to query them and do essentially the same thing for each?) Just sounds like a tough go.

viresh29 Ten Centuries Points: 1111 More actions · Answer 2

The reason is that data coming that are very large more than billions of records for each client.

So I can't put it in same table.

I created SSIS packsges which will work for all client.

I am using TEMPLATE because in my process I want to see data loading perfectly in TEMPLATE

If It does then I will directly load it to Client table.

I already create sql query to using CTAS that will create same structure as Template

But my manager wants me to create sp some way that if we cahnge just Client name instead of Template.

so if we execute once changing Client name. so It would be easy for us.

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

viresh29 Ten Centuries Points: 1111 More actions · Answer 3

If any way I can create variable in SSIS and change table name???

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding