Home / Questions / 497707
Accepted
Henriquez10
Asked: 2022-04-17 22:44:16 +0800 CST 2022-04-17 22:44:16 +0800 CST

How to bind a statement with inner join?

  • 772

This is the first table:

CREATE TABLE dbo.PSDocumentosCarga
(nId int  identity (1,1)not null,
nIdDocumento int   NULL,
nContratoP char(14)  NULL,
cNombreDoc varchar(max)  NULL,
cRutaDoc varchar(max)  NULL,
cCodDoc varchar(max)  NULL,
CONSTRAINT pk1 PRIMARY KEY(nId)
)
GO

This is my second table:

CREATE TABLE dbo.PSDocumentosCompaginar
(nId int primary key identity (1,1),
nIdDocumento int  NULL,
cDocumentoBynario varbinary(max)  NULL,
cNomCampo varchar(250) NULL,
cCodDoc varchar(max) NULL,
constraint fk1 foreign key (nId)
references dbo.PSDocumentosCarga(nId)
)
GO

My insert into query to query and save to my second table

    INSERT INTO PSDocumentosCompaginar  (nIdDocumento,cDocumentoBynario, cNomCampo,cCodDoc)
    SELECT 5, BULKCOLUMN, 'AUTORIZACION BURO', 'AB005'
    FROM OPENROWSET(BULK 'C:\Users\ADMIN\Documents\Queries\Carga de Datos\B0020000000138\AB005.pdf', SINGLE_BLOB) AS DOCUMENT
inner join PSDocumentosCompaginar  C on C.nIdDocumento= PSDocumentosCarga.nIdDocumento

I try to add a inner join, to relate the fields nIdDocumento, but it returns an error.

Msg 4104, Level 16, State 1, Line 55 The multi-part identifier "PSDocumentosCarga.nIdDocumento" could not be bound.

sql

1 Answers

  1. Best Answer

    What the select contains does not yet belong to PSDocumentosCompaginarso it cannot know how to join the table.

    The simplest solution is to use a derived table to have the column names:

      INSERT INTO PSDocumentosCompaginar  (nIdDocumento,cDocumentoBynario, cNomCampo,cCodDoc)
    Select d.* from (
    SELECT 5 AS nIdDocumento, BULKCOLUMN as cDocumentoBynario , 'AUTORIZACION BURO' as cNomCampo, 'AB005' as cCodDoc
    FROM OPENROWSET(BULK 'd:\temp\processlist.txt', SINGLE_BLOB) AS DOCUMENT
    ) as d
    inner join PSDocumentosCompaginar  C on C.nIdDocumento= d.nIdDocumento;
    • 1