Post reply

While Loop

  • September 21, 2009 at 5:15 am

    #138214

    Hi,

    I have a list of tables which I want to copy to another database. I have done a while loop which logically seems fine but when it gets to

    the final part of passing the variable to create the tables I get error message.

    Help Please:-D

    DECLARE

    @TableName varchar (50),

    @RowCounter INT,

    @UpperLimit INT,

    @MinRowID int,

    @MaxRowID int

    SELECT

    @MinRowID = 1,

    @MaxRowID = 1,

    @RowCounter = 1

    --Populate temp table with a list of tables needed to be copied

    drop table #Temp

    SELECT DISTINCT

    TABLEID

    ,TableName

    INTO #Temp

    FROM TaleData

    WHERE TABLEID BETWEEN @MinRowID AND @MaxRowID

    --Get the total rows into a variable

    SET @UpperLimit = @@ROWCOUNT

    --SELECT @MaxRowID

    WHILE @RowCounter <= @UpperLimit
    BEGIN
    SELECT
    @TableName = TableName
    FROM #Temp
    WHERE TABLEID = @RowCounter

    -- Concatenate print the current account month in the variables.
    PRINT 'START: INSERT ' + CAST(@TableName AS CHAR(30))

    --Creat table
    SELECT * INTO dbo.@TableName
    FROM @TableName
    Where cust_ID <= 153
    PRINT 'END ' + 'INSERT Completed for ' + CAST(@TableName AS CHAR(30))

    -- Move to the next row by adding 1 to the row counter
    SET @RowCounter = @RowCounter + 1
    END

  • September 21, 2009 at 6:48 am

    #1056184

    You need to use dynamic SQL. Something like:

    DECLARE @SQL varchar(1000)

    SET @SQL = 'SELECT * INTO dbo.' + @TableName + ' FROM ' + @TableName + ' Where cust_ID <= 153'

    EXECUTE(@SQL)

    However, if the schema of your source table id dbo, this won't work as you're trying to select a table into itself.

    HTH

  • September 21, 2009 at 6:48 am

    #1056186

    You didn't provide the error message you are getting, but I can see the problem. You can't use variables in the SELECT INTO the way you are attempting. You need to use dynamic sql to accomplish this task. Please start by reading about dynamic sql in BOL (Books Online, the SQL Server Help System).

    I am getting ready to leave for work right now, but if no one else has shown you how you could do this with dynamic sql and I have some time at work, I'll provide a little more assistance there.

  • September 21, 2009 at 6:51 am

    #1056189

    Is this is the real code that you use? If it is, then I don’t think that it can even compile. You are trying to create a table variable with create into statement. This can’t be done. You should use a temporary table instead of a table variable.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 21, 2009 at 7:24 am

    #1056207

    I used the dynamic sql as suggested and it works perfectly.

    Thanks alot for your help 😉

  • September 21, 2009 at 8:23 am

    #1056251

    Nice to hear, thanks for the feedback.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply