none
is it possible to insert into a table from a variable? RRS feed

  • Question

  • Suppose i have two tables:

    Table 1 ($SYSTEM.DISCOVER_SESSIONS) has columns A, B, C

    Table 2 has columns A, C, D

    I query table 1 to select data from columns A and C

    I save that query result in a variable called: $result

    essentially it looks like this:

    $result = 'SELECT [A],[C] FROM $SYSTEM.DISCOVER_SESSIONS'

    Now i want to INSERT $result in table 2, with columns automatically matching (A, C) since these were the ones stored in $result

    I realized I could potentially use JOINS for this, but I am unsure how I can formulate the correct syntax for what Im trying to do.

    INSERT INNER JOIN SELECT *
    INTO table2 
    FROM $result  

    Is the approach Im trying to do possible?

    Tuesday, January 29, 2019 7:13 PM

Answers


  • but $result does store columns from the query and export to csv the table. So it does hold a table like structure, which should be accessible easily from the next query:

    INSERT INNER JOIN SELECT *
    INTO table2 
    FROM $result  

    $result is a variable in your application code with the results of a SELECT query. You can't use that variable directly in a T-SQL statement since SQL Server can't access that data directly. You can insert the data directly into the table using SqlBulkCopy if the variable is a data table. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, January 30, 2019 3:19 AM
    Moderator

All replies

  • Your syntax is for MySQL, not MICROSOFT SQL Server.  I suggest you post your question on the MySQL forums.

    https://forums.mysql.com/

    Tuesday, January 29, 2019 7:44 PM
    Moderator
  • Your syntax is for MySQL, not MICROSOFT SQL Server.  I suggest you post your question on the MySQL forums.

    https://forums.mysql.com/

    How is it mysql...i am not using mysql at all....Im literally connectiong to SQL through this function:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$usageDB;Integrated Security=SSPI" 

    Tuesday, January 29, 2019 7:53 PM
  • I guess Tom assumed you were using something else than SQL Server, since your code did not make much sense in a pure T-SQL perspective. I'm not sure what it is supposed to be, but maybe it is PowerShell.

    That $result of yours is a PowerShell object, and you cannot start to use that in SQL. Keep in mind that your PowerShell runs in one process, and SQL Server runs a completely different process, which could be on a completely different server. SQL Server knows nothing about your $result.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 29, 2019 10:58 PM
    Moderator
  • I guess Tom assumed you were using something else than SQL Server, since your code did not make much sense in a pure T-SQL perspective. I'm not sure what it is supposed to be, but maybe it is PowerShell.

    That $result of yours is a PowerShell object, and you cannot start to use that in SQL. Keep in mind that your PowerShell runs in one process, and SQL Server runs a completely different process, which could be on a completely different server. SQL Server knows nothing about your $result.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    but $result does store columns from the query and export to csv the table. So it does hold a table like structure, which should be accessible easily from the next query:

    INSERT INNER JOIN SELECT *
    INTO table2 
    FROM $result  

    Tuesday, January 29, 2019 11:19 PM

  • but $result does store columns from the query and export to csv the table. So it does hold a table like structure, which should be accessible easily from the next query:

    INSERT INNER JOIN SELECT *
    INTO table2 
    FROM $result  

    $result is a variable in your application code with the results of a SELECT query. You can't use that variable directly in a T-SQL statement since SQL Server can't access that data directly. You can insert the data directly into the table using SqlBulkCopy if the variable is a data table. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, January 30, 2019 3:19 AM
    Moderator