October 23, 2008 at 8:45 pm
How do I create a table name using a variable? For example, I want to split a large table into smaller tables. I would like to create X number of tables based on how many records are in the master table. So if my master table has 40,000 records and I want to split this into 4 tables with 10,000 records each. I want to loop through and create each table with a name of Table1, Table2, etc..
I tried to create a @counter parameter and use a Create Table+@counter but that gives me an error.
Is there a way to loop through a counter and create a table name with a number appended to the end of the name? (Table1, Table2, etc..)
Can someone point me in the right direction?
Thanks.
October 23, 2008 at 10:17 pm
You can do it using Dynamic SQL. The more important question is why you need to do it at all. 40K rows is awfully small to look at splitting a table. Why do you feel this table needs to be split?
October 24, 2008 at 7:26 am
Thanks for the info. The only reason I am doing this is from another program limitation that can only process 10,000 records at a time. It is an e-mail campaign program and we have about 40,000 customers signed up for our e-mail list but our current software will only process 10,000 at a time. So I split them into 4 Tables. I'm trying to find a way to do this automatically since our list changes with new subscribers and unsubscribers so I don't have to manually split these tables each time we want to run a campaign.
October 24, 2008 at 8:01 am
Do you necessarily need to create tables for them though?
or if you really do, select all 40000 into 1 temp table with Identity column
work on all 40000 records at once
then for the email program, just figure out the ID ranges, and work on 1-10000, 10001~20000, etc.. (kept looping until no more records)
October 24, 2008 at 9:06 am
Unfortunately, the software we use doesn't allow scripting. It just asks for the table name and email field to use. I can't update this program to allow stepping through the records. If I choose the table with all the records, it errors that the number of records is too large to process. (maybe we should look into updating our program we use for email campaigns).
The masterlist does have an id field that is numbered so when I manually split them, I just select records with id from 1-10000, 10001-20000, etc..
I'll do some searching on dynamic sql and see if I can find something or I will just split them manually until I either find something or update our program.
October 24, 2008 at 9:21 am
Have you tried the same trick using a view?
October 24, 2008 at 10:17 am
As Ninja said, views are normally used to solve this kind of issue.
CREATE VIEW EmailList1
AS
SELECT *
FROM EmailList
WHERE ID < 10000
CREATE VIEW EmailList2
AS
SELECT *
FROM EmailList
WHERE ID BETWEEN 10000 AND 19999
October 24, 2008 at 10:41 am
Or, possibly create a view as:
CREATE VIEW dbo.vEmailList AS
SELECT TOP(10000)
column1
,column2
,...
FROM dbo.EmailList
GO;
Your application just accesses the view until there is nothing left.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2008 at 10:45 am
Thanks for the info. This is what I am currently doing (except I am not using a view) I am just creating each table with a script similar to this.
What I was trying to acomplish was a way to automatically create the table name. Right now I am just creating 4 tables with id 0-10000 in Table1, 10001-2000 in Table2, etc...
What I was trying to acomplish is a way to find out how many records are in the mastertable and dynamically create x number of tables.
Something like this:
DECLARE @TableNo INT
SET @TableNo = ((select count(*) from MasterList) / 10000)
DECLARE @TableCount Int
SET @TableCount = 1
DECLARE @MasterName varchar(50)
DECLARE @SQL nvarchar(500)
WHILE @TableCount <= @TableNo + 1
BEGIN
SET @MasterName = 'MasterList'+@TableCount+''
SET @SQL = 'DROP TABLE ' + @MasterName+''
exec sp_executesql @SQL
SET @SQL = 'Select email into ' + @MasterName + ' from
MasterList where id_num
between (10000 * (@TableCount - 1)) and (10000 * @TableCount)'
exec sp_executesql @SQL
SET @TableCount = @TableCount + 1
END
I don't know how to create the table name dynamically. How to add the 1,2,3, etc. at the end of the table name.
October 24, 2008 at 11:46 am
DECLARE @TableNo INT
SET @TableNo = ((select count(*) from MasterList) / 10000)
DECLARE @TableCount Int
SET @TableCount = 1
DECLARE @MasterName varchar(50)
DECLARE @SQL nvarchar(500)
WHILE @TableCount <= @TableNo + 1
BEGIN
SET @MasterName = 'MasterList'+@TableCount+''
SET @SQL = 'DROP TABLE ' + @MasterName+''
exec sp_executesql @SQL
SET @SQL = 'Select email into ' + @MasterName + ' from
MasterList where id_num
between (10000 * (@TableCount - 1)) and (10000 * @TableCount)'
exec sp_executesql @SQL
SET @TableCount = @TableCount + 1
Doesn't your statement add the 1, 2, 3, to the suffix already?
SET @MasterName = 'MasterList'+@TableCount+''
Anyway, the flow should be
Create Table1, Populate it, Drop Table1
Create Table2, Populate it, Drop Table2
...
(or you can drop all tables in the end)
October 24, 2008 at 11:50 am
When I try to execute this I receive the following error:
"Syntax error converting the varchar value 'MasterList' to a column of data type int."
Any suggestion what this error is?
Thanks.
October 24, 2008 at 11:53 am
That error likely comes from this:
SET @MasterName = 'MasterList'+@TableCount+''
which needs to be:
SET @MasterName = 'MasterList'+CAST(@TableCount as varchar(5)) +''
October 24, 2008 at 11:58 am
Great! That worked to add the number at the end.
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply