将多个文件插入到具有相同ID的另一个表中 [英] to insert more than 1 files to another table with same id

问题描述

任何人都可以帮助我..我想将表格中的文件插入另一张表格。问题是，当我尝试使用相同的cmpntid插入多个文件时，它只会插入1个文件，因为一旦存储过程运行，我只生成一个主键。我如何更改我的代码，以便我可以插入多个文件意味着为上传的每个附加文件生成另一个主键（ComplaintFileID）？这是我的存储过程

can anyone help me..i wanna insert files from a table to another table. the problem is, when i try to insert more than 1 file with the same cmpntid, it will only insert 1 file only since I generate only one primary key once the stored procedure is running. how do i alter my code so that i can insert more than 1 file means to generate another primary key (ComplaintFileID) for each additional file uploaded..? here is my stored procedure

DECLARE @MaxFile nvarchar(50)
DECLARE @MaxFileID nvarchar(50)


SELECT @MaxFileID = ISNULL(MAX(ISNULL(RNo,0)),0)+1 FROM Enforcement.Aduan.ComplaintFiles
SET @MaxFile = 'F' + @MaxFileID
	

INSERT INTO DB_B.CmpntFiles  (ComplaintFileID, ComplaintID , FileName ,Description ,FileExtension , FileType , Cid, Cdt)
SELECT @MaxFile, CmpntID, FileName, Description, FileExtension, FileType, Cid, Cdt FROM DB_A.ComplaintFiles 
WHERE CmpntID = @CmpntID

here是我的表结构（涉及来自不同数据库的表）：



DB_A：表CmpntFiles

CmpntID

FileName

描述

FileExtension

FileType

Cid

cdt







DB_B：表格CmpntFiles

ComplaintFileID

投诉ID

文件名

描述

FileExtension

FileType

Cid

Cdt

here are my table structure (involve table from different database):

DB_A:Table CmpntFiles
CmpntID
FileName
Description
FileExtension
FileType
Cid
Cdt



DB_B:Table CmpntFiles
ComplaintFileID
ComplaintID
FileName
Description
FileExtension
FileType
Cid
Cdt

推荐答案

看一下表的定义：

Have a look at the definition of table:

CREATE TABLE [Aduan].[ComplaintFiles] (
    [ComplaintFileID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    ...

我建议你在你的<省略 ComplaintFileID 字段code> INSERT 语句，因为它是 IDENTITY [< a href =http://msdn.microsoft.com/en-us/library/ms186775.aspxtarget =_ blanktitle =New Window> ^ ]字段。这意味着每个新行的值都会被添加并根据之前的值增加;）

I would suggest you to omit ComplaintFileID field in your INSERT statement because it is IDENTITY[^] field. This mean that value for each new row is added and increases based on previous value ;)

INSERT INTO DB_B.CmpntFiles  (ComplaintID , FileName ,Description ,FileExtension , FileType , Cid, Cdt)
SELECT CmpntID, FileName, Description, FileExtension, FileType, Cid, Cdt FROM DB_A.ComplaintFiles
WHERE CmpntID = @CmpntID

我们清楚了吗？

Are we clear?

试试这样的东西......

Try Something Like this...

INSERT INTO TableName (PrimaryKeyColumn,Columns...)
SELECT @Id+ROW_NUMBER() Over(Order by ColumnNames), Columns from TableName 
WHERE Cond'n

DECLARE @MaxFile nvarchar(50), @MaxFileID nvarchar(50)

SELECT @MaxFileID = ISNULL(MAX(ISNULL(RNo,0)),0) FROM Enforcement.Aduan.ComplaintFiles
SET @MaxFile = 'F' + @MaxFileID

INSERT INTO Enforcement.Aduan.ComplaintFiles (ComplaintFileID, ComplaintID, FileName, Description, FileExtension, FileType, Cid, Cdt)
SELECT @MaxFile+ Cast(ROW_NUMBER() Over(Order by ComplaintID) as Nvarchar), ComplaintID, FileName, Description, FileExtension, FileType, Cid, Cdt FROM saoCmpntFile
WHERE CmpntID = @CmpntID

请检查此链接：主键-W3学校

主要和外围密钥MSDN

这篇关于将多个文件插入到具有相同ID的另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！