May 11, 2012 at 7:12 am
Hi Ive made a stored procedure. and it returns into an error as written on the topic.
Here is my procedure.
CREATE PROC usp_splitoutputfile @tablename VARCHAR(200), @volume int,@Splits INT
AS
BEGIN
IF @Splits = 1
BEGIN
PRINT 'SELECT TOP'+@volume+' * INTO dbo.'+@tablename+'_1'''
END
END
Here is the Error Message: Msg 245, Level 16, State 1, Procedure usp_splitoutputfile, Line 8
Conversion failed when converting the varchar value 'SELECT top '' to data type int.
May 11, 2012 at 8:26 am
Thomas Stringer (5/11/2012)
Try this:
CREATE PROC usp_splitoutputfile
@tablename VARCHAR(200), @volume int,@Splits INT
AS
BEGIN
IF @Splits = 1
BEGIN
PRINT 'SELECT TOP' + cast(@volume as varchar(100)) + ' * INTO dbo.'+@tablename+'_1'''
END
END
One very slight change should you change the PRINT to EXEC:
CREATE PROC usp_splitoutputfile
@tablename VARCHAR(200), @volume int,@Splits INT
AS
BEGIN
IF @Splits = 1
BEGIN
PRINT 'SELECT TOP ' + cast(@volume as varchar(100)) + ' * INTO dbo.'+@tablename+'_1'''
END
END
Note the space added after TOP.
May 22, 2012 at 9:07 am
thank you so much.. This solves my problem.. Now I have a new problem.
I want to split a table into multiple table.
what I need is to print a code that goes like this
/*
SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_1 FROM LR_TABLE_TEST ORDER BY NEWID()
SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_2 FROM LR_TABLE_TEST WHERE guidsource NOT IN (
SELECT guidsource FROM LR_TABLE_TEST_1
) ORDER BY NEWID()
SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_3 FROM LR_TABLE_TEST WHERE guidsource NOT IN (
SELECT guidsource FROM LR_TABLE_TEST_1 UNION ALL
SELECT guidsource FROM LR_TABLE_TEST_2
) ORDER BY NEWID()
SELECT * INTO dbo.LR_TABLE_TEST_4 FROM LR_TABLE_TEST WHERE guidsource NOT IN (
SELECT guidsource FROM LR_TABLE_TEST_1 UNION ALL
SELECT guidsource FROM LR_TABLE_TEST_2 UNION ALL
SELECT guidsource FROM LR_TABLE_TEST_3
) ORDER BY NEWID()
*/
I have managed to make the first and second statement (SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_1 FROM LR_TABLE_TEST ORDER BY NEWID()
SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_2 FROM LR_TABLE_TEST WHERE guidsource NOT IN (
SELECT guidsource FROM LR_TABLE_TEST_1
) ORDER BY NEWID() )
what i need is how to insert a record into the third and succeding table which records are not in the previous table as shown above. and to select * records(not top 50000*) into the final table I am stock on this I need a help . thank you somuch
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply