SQL Server Performance

SQL2005 Insert getting 1 field from another table

Discussion in 'ALL SQL SERVER QUESTIONS' started by Garabed, May 10, 2012.

  1. Garabed New Member

    I am trying to do an insert with partly data that I define and a 1 field that comes from another table. I currently have :

    INSERT INTO Tablea
    (D,yID,CID,TC,TN,SpType,Alert,DateCreated,CreatedBy)
    Values(101393,178973,(Select CID from tableb tc WHERE tc.CID=178973 AND TC.CD='ALPHA/COMBO'),'120766','(CRP)','GEL','',getdate(),'me')

    Being a SQL beginner I come to find out this does not work in 2005. Anyone have any ideas?
  2. Luis Martin Moderator

    Welcome to the forums!.
    May the following example will help you:
    G. Inserting data using the SELECT and EXECUTE options

    The following example shows three different methods for getting data from one table and inserting it into another. Each is based on a multitable SELECT statement that includes an expression and a literal value in the column list.
    The first INSERT statement uses a SELECT statement directly to retrieve data from the source tables (Employee, SalesPerson, and Contact) and store the result set in the EmployeeSales table. The second INSERT executes a stored procedure that contains the SELECT statement, and the third INSERT executes the SELECT statement as a literal string.

    USE AdventureWorks;
    GO
    IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
    GO
    IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
    GO
    CREATE TABLE dbo.EmployeeSales
    ( DataSource varchar(20) NOT NULL,
    EmployeeID varchar(11) NOT NULL,
    LastName varchar(40) NOT NULL,
    SalesDollars money NOT NULL
    );
    GO
    CREATE PROCEDURE dbo.uspGetEmployeeSales
    AS
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName,
    sp.SalesYTD
    FROM HumanResources.Employee AS e
    INNER JOIN Sales.SalesPerson AS sp
    ON e.EmployeeID = sp.SalesPersonID
    INNER JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
    GO
    --INSERT...SELECT example
    INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD
    FROM HumanResources.Employee AS e
    INNER JOIN Sales.SalesPerson AS sp
    ON e.EmployeeID = sp.SalesPersonID
    INNER JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
    GO
    --INSERT...EXECUTE procedure example
    INSERT EmployeeSales
    EXECUTE uspGetEmployeeSales;
    GO
    --INSERT...EXECUTE('string') example
    INSERT EmployeeSales
    EXECUTE
    ('
    SELECT ''EXEC STRING'', e.EmployeeID, c.LastName,
    sp.SalesYTD
    FROM HumanResources.Employee AS e
    INNER JOIN Sales.SalesPerson AS sp
    ON e.EmployeeID = sp.SalesPersonID
    INNER JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
    ');
    GO
    --Show results.
    SELECT DataSource,EmployeeID,LastName,SalesDollars
    FROM dbo.EmployeeSales;
    GO
  3. Garabed New Member

    Thank you for your reply however it looks as if you are retrieving all of the data you are inserting from tables. Only 1 field "CD" comes from another table the rest does not.
  4. FrankKalis Moderator

    Looks like you are looking for something like this:
    Code:
    INSERT INTO Tablea
        (D,
        yID,
        CID,
        TC,
        TN,
        SpType,
        Alert,
        DateCreated,
        CreatedBy)
    SELECT
        101393,
        CID,
        CID ,
        '120766',
        '(CRP)',
        'GEL',
        '',
        getdate(),
        'me'
    FROM
        tableb tc
    WHERE
        tc.CID=178973 AND
        TC.CD='ALPHA/COMBO';
  5. Garabed New Member

    That was it.

    Thank you for your assistance.

Share This Page