[scott2500uk]

We currently have an INSERT query to insert data from a SELECT from an OPENQUERY. This works well but what we want to do is add a column with a fixed value which we will use as an identifier.

IS there anyway we can do this to the below query?

Want to add a column called [PHPCheck] and load in the fixed value from @PHPCheck

Code:

--Generate ADP Query
Set @ADPQuery1 ='INSERT INTO ADPDL_Stage ('
Set @ADPQuery2 ='[DIVMY],[AC_CODE],[Value],[ADP_Desc]) Select * from OPENQUERY(ADP,'
Set @ADPQuery3 = '''SELECT NL_00_NominalDepartmentCodes.AccountingDivision, NL_00_NominalAccountCodes.AccountCode, NL_00_NominalAccountCodes.BroughtForward001+NL_00_NominalAccountCodes.CurrentPostings001 BFandCP, NL_00_NominalExpenseCodes.Description001
FROM NL_00_NominalAccountCodes NL_00_NominalAccountCodes, NL_00_NominalDepartmentCodes NL_00_NominalDepartmentCodes, NL_00_NominalExpenseCodes NL_00_NominalExpenseCodes
WHERE NL_00_NominalDepartmentCodes.CostCentre = NL_00_NominalAccountCodes.CostCentre AND NL_00_NominalAccountCodes.ExpenseCode = NL_00_NominalExpenseCodes.ExpenseCode  
AND NL_00_NominalDepartmentCodes.AccountingDivision = '
Set @ADPQuery4 = 
'AND (Broughtforward001 <> 0.00 AND CurrentPostings001 <> 0.00)
ORDER BY NL_00_NominalAccountCodes.AccountCode'');'

-- Inserting data from the adptemp table
exec (@ADPQuery1 + @ADPQuery2  + @Adpquery3 + @adpdiv + @ADPQuery4)

Any help is greatly appreciated

Scott

[scott2500uk]

NVM I've found out how to do this by changing the SELECT * FROM to SELECT column1,column2....'value' FROM

Code:

--Generate ADP Query
Set @ADPQuery1 ='INSERT INTO ADPDL_Stage ('
Set @ADPQuery2 ='[DIVMY],[AC_CODE],[Value],[ADP_Desc],[Month],[Year],[PHPCheck]) SELECT AccountingDivision,AccountCode,BFandCP,Description001,'''+@ADPMth+''','''+@ADPYear+''','''+@PHPCheck+''' FROM OPENQUERY(ADP,'
Set @ADPQuery3 = '''SELECT NL_00_NominalDepartmentCodes.AccountingDivision, NL_00_NominalAccountCodes.AccountCode, NL_00_NominalAccountCodes.BroughtForward001+NL_00_NominalAccountCodes.CurrentPostings001 BFandCP, NL_00_NominalExpenseCodes.Description001
FROM NL_00_NominalAccountCodes NL_00_NominalAccountCodes, NL_00_NominalDepartmentCodes NL_00_NominalDepartmentCodes, NL_00_NominalExpenseCodes NL_00_NominalExpenseCodes
WHERE NL_00_NominalDepartmentCodes.CostCentre = NL_00_NominalAccountCodes.CostCentre AND NL_00_NominalAccountCodes.ExpenseCode = NL_00_NominalExpenseCodes.ExpenseCode  
AND NL_00_NominalDepartmentCodes.AccountingDivision = '+@ADPDiv+''
Set @ADPQuery4 = 
'AND (Broughtforward001 <> 0.00 AND CurrentPostings001 <> 0.00)
ORDER BY NL_00_NominalAccountCodes.AccountCode'');'

-- Inserting data from the adptemp table
exec (@ADPQuery1 + @ADPQuery2  + @Adpquery3 + @ADPQuery4)