Incorrect Syntax near Select[Answered] RSS

• • Reply
  jsshivalik

  Member

  140 Points

  518 Posts

  Incorrect Syntax near Select

  Nov 12, 2019 06:46 AM|jsshivalik|LINK

  Hi

   I have below query

  declare @Supplier varchar(15);
  Set @Supplier = '23000038' 
  CREATE TABLE #Ledger(
      [CardCode] [varchar](50) NULL,
  	[CardName] [varchar](100) NULL,
      [TransId] [int] NULL,
  	[Account] [int] NULL,
  	[Debit] [int] NULL,
  	[Credit] [int] NULL,
      [PostingDate] Date NULL
  )
  
  INSERT #Ledger ([CardCode], [CardName], [TransId], [Account], [Debit], [Credit], [PostingDate])
   VALUES (select A.CardCode,A.CardName,B.TransId,B.Account,B.Debit,B.Credit,B.Date,B.Name from test1 as A
  inner join test2 as B
  on A.CardCode = B.Name
  where A.CardCode = @Supplier
  )
  drop table #Ledger

  Thanks

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: Incorrect Syntax near Select

  Nov 12, 2019 07:37 AM|Yang Shen|LINK

  Hi jsshivalik,

  Please try remove the VALUES( and ) in your query, you can refer to Insert into … values ( SELECT … FROM … ).

  Your code should be like below:

  declare @Supplier varchar(15);
  Set @Supplier = '23000038' 
  CREATE TABLE #Ledger(
      [CardCode] [varchar](50) NULL,
  	[CardName] [varchar](100) NULL,
      [TransId] [int] NULL,
  	[Account] [int] NULL,
  	[Debit] [int] NULL,
  	[Credit] [int] NULL,
      [PostingDate] Date NULL
  )
  
  INSERT #Ledger ([CardCode], [CardName], [TransId], [Account], [Debit], [Credit], [PostingDate])
  select A.CardCode,A.CardName,B.TransId,B.Account,B.Debit,B.Credit,B.Date,B.Name from test1 as A
  inner join test2 as B
  on A.CardCode = B.Name
  where A.CardCode = @Supplier
  
  drop table #Ledger

  Best Regard,

  Yang Shen