CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Anaiah
Anaiah

Reputation: 633

SQL Server : copy data from columns to another table with many columns

I have an existing table wherein I store my data that I got from another server.

Now, I want to copy that data to one table wherein that will be my final table on where I could do the CRUD operation.

Fist table (reference database) to restore to my TempTable - OK

INSERT INTO [PFTracking].[dbo].[TempTable]
   SELECT 
      c.[pf_id]
      ,a.[RequestDate]
      ,c.[pf_carrierUsed]
      ,b.[PiecePrice] * b.[PartQuantity] as [Amount]
      ,c.[pf_type]
      ,c.[pf_resSupplier]
      ,c.[pf_resCustomer]
      ,c.[pf_trailerNum]
      ,b.[PartDesc]
      ,c.[pf_chargeBack]
      ,c.[pf_chargetoPlant]
   FROM 
      [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a
   JOIN 
      [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] = b.[RequestID]
   JOIN 
      [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c ON  b.[PartNumber] LIKE '%' + c.pf_id + '%'
   WHERE 
      a.[EntityName] LIKE '%PTA' 
      AND a.[RequestDate] BETWEEN '2015-04-20 00:00:00.000' AND GETDATE()

and now .. since my TempTable consists of these columns:

   [PTAID]
  ,[RequestDate]
  ,[ProvName]
  ,[Amount]
  ,[INorOUT]
  ,[Supplier]
  ,[Customer]
  ,[Program]
  ,[IssueDesc]
  ,[Chargeable]
  ,[Company]

I tried to do this command :

INSERT INTO [PFTracking].[dbo].[UserInput]
    SELECT
        [PTAID]
      ,[RequestDate]
      ,[ProvName]
      ,[Amount]
      ,[INorOUT]
      ,[Supplier]
      ,[Customer]
      ,[Program]
      ,[IssueDesc]
      ,[Chargeable]
      ,[Company]
 FROM [PFTracking].[dbo].[TempTable]

and this one :

INSERT INTO [PFTracking].[dbo].[UserInput]
   SELECT *
   FROM [PFTracking].[dbo].[TempTable]

But all I got is this error:

Msg 213, Level 16, State 1, Line 3
Insert Error: Column name or number of supplied values does not match table definition.

Any idea how to solve my problem ?

Upvotes: 0

Views: 87

Answers (1)

Deepshikha
Deepshikha

Reputation: 10264

As a best practice you should write the insert statement as:

Insert into [PFTracking].[dbo].[UserInput](Col1,Col2,...)
-- list the names of columns explicitly in which data is to be inserted
SELECT
        [PTAID]
      ,[RequestDate]
      ,[ProvName]
      ,[Amount]
      ,[INorOUT]
      ,[Supplier]
      ,[Customer]
      ,[Program]
      ,[IssueDesc]
      ,[Chargeable]
      ,[Company]
 FROM [PFTracking].[dbo].[TempTable]

You can omit the column names that get their values automatically from an Identity Property,Default constraint or when allowing Nulls.

Upvotes: 3

Related Questions