Post reply

setting variables using a list

  • October 6, 2004 at 12:53 am

    #161642

    I haven't touch SQL in so long I can't remember much. Here is what I was trying to do. Create a simple two column table using input from other tables. I am trying to have it insert multiple values using variables. I am trying to take the variables from a column in another table. The problem is that the subquery returns multiple values (which I wanted), but it is a syntax error. I need to find a way to have it insert a new line into the new table for each record in the import table. Hope this makes sense. Here is the transact-sql:

    IF OBJECT_ID ('dbo.SUS_Numbers') IS NOT NULL

    DROP TABLE dbo.SUS_Numbers

    GO

    CREATE TABLE dbo.SUS_Numbers

    (

    Number VARCHAR(50)NULL ,

    Total VARCHAR(50) NULL

    )

    GO

    --Insert data

    DECLARE @qnum1 varchar(50)

    SET @qnum1 = (select col1 from dbo.import)

    DECLARE @qnum2 varchar(50)

    SET @qnum2 = (select col2 from dbo.import)

    DECLARE @tot varchar(50)

    SET @tot = (Select count(*) FROM dbo.sus_data where (ItemInfo LIKE @qnum2) and (Activity = 'Download') AND (Status = 'Succeeded'))

    INSERT INTO dbo.SUS_Numbers (Number, Total)

    VALUES (@qnum1, @tot)

    GO

    --View data

    SELECT * from dbo.SUS_Numbers

    GO

  • October 6, 2004 at 1:55 am

    #525545

    You should do something like this:

    INSERT INTO dbo.SUS_Numbers (Number, Total)
SELECT col1, (SELECT count(*) FROM dbo.sus_data
 WHERE ItemInfo LIKE dbo.Import.col2
 AND Activity='Download' AND Status='Succeeded') as Total
FROM dbo.Import

    Razvan

Viewing 2 posts - 1 through 1 (of 1 total)

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