Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Stored procedure+ insert query result to another table

Answered Stored procedure+ insert query result to another table

  • Friday, February 15, 2013 6:01 AM
     
     
    Sign In to Vote
    0

    I am a newbie to database. I am trying to create stored procedure to insert same data to more than one table using variable. So my code goes like this :

    CREATE PROCEDURE test_abc
    AS
    set NoCount on
    declare @test as varchar(255)

    INSERT into openquery(mysql, 'select name from test')
    select cast(description as varchar(max)) from table1 where Type = '3201'

    set @test = (select (c.cname COLLATE DATABASE_DEFAULT + '     ' COLLATE DATABASE_DEFAULT + a.Description COLLATE DATABASE_DEFAULT) as result from table1 as a left join table2 as c on a.CustomerID= c.cmp_wwn  where a.Type = '3201')

    INSERT INTO OPENQUERY (mysql, 'SELECT description from test_customer_data') select @test

    end

    This is not working, there is some concept with variable that I'm missing i guess.. can anyone help?

     

All Replies

  • Friday, February 15, 2013 8:28 AM
     
     
    Sign In to Vote
    0

    What does "is not working" mean? Do you get unexpected results? Do you get an error message? In such case, what does it say?

    Getting things like this to work can be difficult, particularly when the linked server is a different product. How did you set up the linked server? Did you use the OLE DB provider for MySQL?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, February 15, 2013 9:11 AM
     
     
    Sign In to Vote
    0

    Why you make this complicated for multiple table insert ?

    Send me tables list and from where data need to be taken ..

    I hope u'll provide much better response.

    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

     
  • Sunday, February 17, 2013 8:23 PM
     
     
    Sign In to Vote
    0

    Thanks for the reply..

    scenario: insert customer_name from table1 at mssql to table2 at mysql. Then, insert the corresponding customer_id of the inserted customer_name(which we inserted to table2 just b4), along with ticket, type and status from table1 at mssql to table3 at mysql. Thing to be noted is customer_id is not in table1. I have to pass customer_id from table2 at mysql and pass other columns (ticket, type and status) from table1 at mssql to table3 at mysql same time (i mean in 1 insert)

    • Edited by KJayd Monday, February 18, 2013 2:28 AM
    •  
     
  • Sunday, February 17, 2013 10:27 PM
     
     
    Sign In to Vote
    0

    Error is :

    Msg 512, Level 16, State 1, Procedure test_abc, Line 11
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Linked server is working well. set up linked server through odbc driver..

     
  • Monday, February 18, 2013 2:24 AM
     
     
    Sign In to Vote
    0

    You are trying to assign @test from a query:

    (select (c.cname COLLATE DATABASE_DEFAULT + '     ' COLLATE DATABASE_DEFAULT + a.Description COLLATE DATABASE_DEFAULT) as result from table1 as a left join table2 as c on a.CustomerID= c.cmp_wwn  where a.Type = '3201')

    This query returns more than one row, but this is not legal in this context. If you say:

      SELECT @test = (SELECT ...)

    The subquery must return 0 rows or 1 row. It must not return multiple rows.

    You will need to investigate why the query returns multiple rows.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, February 18, 2013 10:22 PM
     
     
    Sign In to Vote
    0
    Thanks for that.. I want to assign result of the query to a variable. and the result of the query will have many number of rows. later insert all those rows to other table with the help of this one variable..
     
  • Tuesday, February 19, 2013 1:32 AM
     
     Answered
    Sign In to Vote
    0

    Thanks for that.. I want to assign result of the query to a variable. and the result of the query will have many number of rows. later insert all those rows to other table with the help of this one variable..

    You have declared @test as carchar(255). This means that @test can contain a single scalar value of total 255 characters.

    You should rather user a table variable or a temp table, and insert data into that table with an INSERT statement.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked As Answer by KJayd Wednesday, February 20, 2013 6:09 AM
    •