0

I am trying to insert data from an existing table into a File table. However I just need 2 fields, FileName and Id from that table.

I am trying to create a stored procedure and use it.

insert into bomFile (AtgtId, FileName)
select CAST(FileName AS nvarchar(255)), AtgtId 
from bomApplicationImportTgt;

I want to insert all the records of FileName and AtgtId from bomApplicationImportTgt table into bomFile table.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Minhal
  • 85
  • 2
  • 17

1 Answers1

1

Your select and insert stmt are not in sync. The order of params in the insert and what you're selecting to insert don't match.

change select CAST(FileName AS nvarchar(255)), AtgtId to

select AtgtId, CAST(FileName AS nvarchar(255))

FizzBuzz
  • 683
  • 3
  • 8
  • Thanks it actually answered my question. but now I have another issue in which the table File in which I am entering this data has a fileid which is a primary key. So it is not taking null values for it – Minhal May 06 '19 at 19:12
  • Cannot insert the value NULL into column 'FileId', table 'Dev Purchasing New Model.dbo.bomFile'; column does not allow nulls. INSERT fails. The statement has been terminated. – Minhal May 06 '19 at 19:12
  • This column FileId was not mentioned in your db structure. You apparently have a non null field on this table that you either need to insert a value into or set as an auto incrementing IDENTITY column – FizzBuzz May 06 '19 at 20:31
  • I am trying to do it in this way now. – Minhal May 07 '19 at 11:56
  • insert into bomFile (AtgtId, FileName, FileId) values ('','',1) select AtgtId, CAST(FileName AS nvarchar(255)) from bomApplicationImportTgt; – Minhal May 07 '19 at 11:56
  • but it gives an error saying that it conflicts with a foreign key of AtgtId on bomApplicationImportTgt table. – Minhal May 07 '19 at 11:57
  • The column field is a non null field and it is already set to identity so it is auto incremented but still gives the same error – Minhal May 07 '19 at 11:58
  • You don't insert into an identity column – FizzBuzz May 07 '19 at 14:10
  • @Minhal you should create another question rather than trying to resolve this new issue in comments. FizzBuzz has answered the original question and his answer should be accepted so the question can be closed. – Tab Alleman May 07 '19 at 14:32
  • @TabAlleman Yeah sure will do that. Thanks fizz buzz for your help. – Minhal May 07 '19 at 14:56