Rename Table After Copy SQL Server Object Task

Rename Table After Copy SQL Server Object Task

Post by Keith Nicholso » Sat, 08 Mar 2003 00:50:21



Good day all!

I have a need each month to import into my SQL DB a data file which is sent
to me.  The text file (txt) comes with the occompaning format file (fmt) for
doing bulk inserts.  This is no problem using the "Bulk Insert Task".

Here is my challange.  For multiple reasons, I have chosen to take this
monthly  "Orders_Detail" file and import it into its own table.  The format
for the name of each new table is "Orders_Detail_MMM_YY".

I thought the easiest way to do this would be to take an existing table
which I call "Orders_Detail_Empty" (stored in a seperate DB) and do a "Copy
SQL Server Object Task" to the correct DB for the monthly records.  I was
suprised to see in the VB code that the only reference to the table itself
is :

        oCustomTask1.AddObjectForTransfer "CALL_DETAIL_Empty", "dbo", 8

There does appear to be a rename method.

I looked into the "Execute SQL Task".  No luck here.  So rather than waste
my time looking for the best option myself, I thought I might ask you.

Any great suggestions?

----
Peace,
Keith Nicholson, MCSD, MCAD

 
 
 

Rename Table After Copy SQL Server Object Task

Post by Keith Nicholso » Sat, 08 Mar 2003 04:06:23


Okay.

I went back to the "Execute SQL Task" and used sp_rename procedure in my SQL
Text.  This works.

--
Peace,
Keith Nicholson, MCSD, MCAD


Quote:> Good day all!

> I have a need each month to import into my SQL DB a data file which is
sent
> to me.  The text file (txt) comes with the occompaning format file (fmt)
for
> doing bulk inserts.  This is no problem using the "Bulk Insert Task".

> Here is my challange.  For multiple reasons, I have chosen to take this
> monthly  "Orders_Detail" file and import it into its own table.  The
format
> for the name of each new table is "Orders_Detail_MMM_YY".

> I thought the easiest way to do this would be to take an existing table
> which I call "Orders_Detail_Empty" (stored in a seperate DB) and do a
"Copy
> SQL Server Object Task" to the correct DB for the monthly records.  I was
> suprised to see in the VB code that the only reference to the table itself
> is :

>         oCustomTask1.AddObjectForTransfer "CALL_DETAIL_Empty", "dbo", 8

> There does appear to be a rename method.

> I looked into the "Execute SQL Task".  No luck here.  So rather than waste
> my time looking for the best option myself, I thought I might ask you.

> Any great suggestions?

> ----
> Peace,
> Keith Nicholson, MCSD, MCAD


 
 
 

Rename Table After Copy SQL Server Object Task

Post by Keith Nicholso » Sat, 08 Mar 2003 05:31:57


Guess what?

The table name does change but the index, PKs, and constraints to not change
their names.

Time to do more research.

--
Peace,
Keith Nicholson, MCSD, MCAD


> Okay.

> I went back to the "Execute SQL Task" and used sp_rename procedure in my
SQL
> Text.  This works.

> --
> Peace,
> Keith Nicholson, MCSD, MCAD



> > Good day all!

> > I have a need each month to import into my SQL DB a data file which is
> sent
> > to me.  The text file (txt) comes with the occompaning format file (fmt)
> for
> > doing bulk inserts.  This is no problem using the "Bulk Insert Task".

> > Here is my challange.  For multiple reasons, I have chosen to take this
> > monthly  "Orders_Detail" file and import it into its own table.  The
> format
> > for the name of each new table is "Orders_Detail_MMM_YY".

> > I thought the easiest way to do this would be to take an existing table
> > which I call "Orders_Detail_Empty" (stored in a seperate DB) and do a
> "Copy
> > SQL Server Object Task" to the correct DB for the monthly records.  I
was
> > suprised to see in the VB code that the only reference to the table
itself
> > is :

> >         oCustomTask1.AddObjectForTransfer "CALL_DETAIL_Empty", "dbo", 8

> > There does appear to be a rename method.

> > I looked into the "Execute SQL Task".  No luck here.  So rather than
waste
> > my time looking for the best option myself, I thought I might ask you.

> > Any great suggestions?

> > ----
> > Peace,
> > Keith Nicholson, MCSD, MCAD