October 3, 2016 at 11:41 pm
I have a request to create and Insert N number of tables on the fly. The value N can be changed based on need.
Basically, I need to split a large table into N number of smaller tables. Here is what I have for now:
-- The below code creates the tables (six in this case):
declare @tblStr varchar(max)
declare @intCtr char(1)
set @intCtr =1
while @intCtr <= 6
begin
set @tblStr = '
IF OBJECT_ID(''[dbo].[TableName'+@intCtr+']'', ''U'') IS NOT NULL
DROP TABLE [dbo].[TableName'+@intCtr+']
CREATE TABLE [dbo].[TableName'+@intCtr+'](
field1,
field2,
.....
fieldz
)
'
set @intCtr = @intCtr + 1
exec(@tblStr)
end
Problem is when I try to insert into these N number of tables dynamically. Here is what I am attempting. The table has a ROWNUM field and I want to use this field to split the records into each of the child tables.
declare @tblStr varchar(max)
set @tblStr = N'
declare @intCtr char(1)
set @intCtr =1
while @intCtr <= 6
begin
SELECT *
INTO [dbo].[TableName'+CAST(@intCtr As nchar(1))+']
FROM
TableName
WHERE ROWNUM % 6 = @intCtr -1
--exec (@tblStr)
set @intCtr = @intCtr + 1
END
'
print @tblStr
exec (@tblStr)
Any directions would be greatful.
Thanks.
October 3, 2016 at 11:47 pm
The first part that creates the tables is not necessary as I had a select * INTO clause in the second. I had it originally for some testing purpose but that works quite well. The problem is during insert.
October 4, 2016 at 4:07 am
DECLARE @tblStr VARCHAR(MAX);
DECLARE @intCtr INT;
SET @intCtr = 1;
WHILE @intCtr <= 6
BEGIN
SET @tblStr = '
IF OBJECT_ID(''[dbo].[SomeTable' + CONVERT(VARCHAR(5), @intCtr) + ']'', ''U'') IS NOT NULL
BEGIN
DROP TABLE [dbo].[SomeTable' + CONVERT(VARCHAR(5), @intCtr) + ']
END;
GO
SELECT *
INTO [dbo].[SomeTable' + CONVERT(VARCHAR(5), @intCtr) + ']
FROM [dbo].[SomeTable]
WHERE SomeTableId % 6 = ' + CONVERT(VARCHAR(5), @intCtr -1) + '
GO
'
PRINT (@tblStr);
--EXEC (@tblStr);
SET @intCtr = @intCtr + 1;
END;
October 4, 2016 at 8:15 am
Wow, Great, thank you! works clean. (I had to remove the GO statements in the SQL) !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply