Dynamically Creating Table Name and Copying To Linked Server Help

I have tables on Server A that are created on a monthly basis. A table name
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)

> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx