to insert more than 1 files to another table with same id

Question

0.00/5 (No votes)

See more:

hi, 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

SQL

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 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

Posted 31-Jul-13 17:33pm

Mare7

Updated 31-Jul-13 21:34pm

v3

Add a Solution

Comments

ArunRajendra 31-Jul-13 23:56pm

Try Row_number option. Here is a link http://msdn.microsoft.com/en-us/library/ms186734.aspx

Mare7 1-Aug-13 0:24am

i'm sorry but i always have problem understanding anything from that same website..huhu. can you explain it to me or give me another link related please..

ArunRajendra 1-Aug-13 1:59am

Can you give me the table structure?

Maciej Los 1-Aug-13 3:11am

Good question!
I would like to see a definition too.

Mare7 1-Aug-13 3:35am

Hi, i already updated my question with my table structure

Raja Sekhar S 1-Aug-13 3:42am

What are the Primary Columns...?

Mare7 1-Aug-13 4:00am

PK for the table in DB_A is none only got unique file name
PK for the table in DB_B is Complaint File ID (which i insert the value @MaxFile)

Maciej Los 1-Aug-13 3:53am

Where is a definition for both tables?

Mare7 1-Aug-13 4:00am

i'm sorry but what do you mean by the definitions?

Maciej Los 1-Aug-13 4:07am

Go to SSMS, choose database, select table, right-click on it and select from drop-down menu: "Script table as" -> "Create to..." -> "New Query Editor".

Mare7 1-Aug-13 4:19am

owh you mean the script. is it really needed coz basically only what i upload is related to the problem. the thing is the script consists of many2 other query but since this project is confidential, this will be all that i can show..so sorry..

Maciej Los 1-Aug-13 4:24am

What is confidential? The structure of table? I do really understand that data can be confidential, but the definition? How can we help you without knowing the table design?

Mare7 1-Aug-13 4:30am

owh sorry2..i thought its my stored procedure script. i didnt read it properly..my bad. wait ill add it in a moment

Mare7 1-Aug-13 4:37am

i dont know how to upload the script here so im gonna paste it here but its gonna be long though..

DB_A
CREATE TABLE [dbo].[saoCmpntFile](
[CmpntID] [bigint] NOT NULL,
[FileName] [varchar](50) NOT NULL,
[ResponseID] [nvarchar](50) NULL,
[Description] [varchar](255) NULL,
[FileType] [varchar](1) NULL,
[FileExtension] [nvarchar](5) NULL,
[Cid] [nvarchar](50) NULL,
[Cdt] [datetime] NULL,
CONSTRAINT [PK_saoCmpntFile] PRIMARY KEY CLUSTERED
(
[CmpntID] ASC,
[FileName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'''R'' + Max(ResponseID)+1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'saoCmpntFile', @level2type=N'COLUMN',@level2name=N'ResponseID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'C- Complainant File or A- Answer from Officer' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'saoCmpntFile', @level2type=N'COLUMN',@level2name=N'FileType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'File Extension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'saoCmpntFile', @level2type=N'COLUMN',@level2name=N'FileExtension'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Login user ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'saoCmpntFile', @level2type=N'COLUMN',@level2name=N'Cid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Created Date. System Date(i.e. Database Server system date)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'saoCmpntFile', @level2type=N'COLUMN',@level2name=N'Cdt'
GO

ALTER TABLE [dbo].[saoCmpntFile] WITH CHECK ADD CONSTRAINT [FK_saoCmpntFile_AduanResponse] FOREIGN KEY([ResponseID])
REFERENCES [dbo].[AduanResponse] ([ResponseID])
GO

ALTER TABLE [dbo].[saoCmpntFile] CHECK CONSTRAINT [FK_saoCmpntFile_AduanResponse]
GO

ALTER TABLE [dbo].[saoCmpntFile] WITH CHECK ADD CONSTRAINT [FK_saoCmpntFile_saoCmpnt] FOREIGN KEY([CmpntID])
REFERENCES [dbo].[saoCmpnt] ([CmpntID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[saoCmpntFile] CHECK CONSTRAINT [FK_saoCmpntFile_saoCmpnt]
GO

ALTER TABLE [dbo].[saoCmpntFile] ADD CONSTRAINT [DF__saoCmpntF__FileT__7C055DC1] DEFAULT ('C') FOR [FileType]
GO

Mare7 1-Aug-13 4:39am

DB_B
CREATE TABLE [Aduan].[ComplaintFiles](
[ComplaintFileID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ComplaintID] [nvarchar](50) NOT NULL,
[ResponseID] [nvarchar](50) NULL,
[FileName] [varchar](100) NOT NULL,
[Description] [varchar](255) NOT NULL,
[FileExtension] [nvarchar](5) NOT NULL,
[FileType] [nvarchar](5) NOT NULL,
[Cid] [nvarchar](50) NOT NULL,
[Cdt] [datetime] NOT NULL,
CONSTRAINT [PK_ComplaintFiles] PRIMARY KEY CLUSTERED
(
[ComplaintFileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Refer to Complaint.ComplaintID' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'ComplaintID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Refer to ComplaintResponse.ResponseID. If attachment done during Response Conversation store the ResponseID' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'ResponseID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Desciption of the file entered by user' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'File Extension' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'FileExtension'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'C- Complainant File or A- Answer from Officer' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'FileType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Login user ID' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'Cid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Created Date. System Date(i.e. Database Server system date)' , @level0type=N'SCHEMA',@level0name=N'Aduan', @level1type=N'TABLE',@level1name=N'ComplaintFiles', @level2type=N'COLUMN',@level2name=N'Cdt'
GO

ALTER TABLE [Aduan].[ComplaintFiles] WITH CHECK ADD CONSTRAINT [FK_ComplaintFiles_Complaint] FOREIGN KEY([ComplaintID])
REFERENCES [Aduan].[Complaint] ([ComplaintID])
GO

ALTER TABLE [Aduan].[ComplaintFiles] CHECK CONSTRAINT [FK_ComplaintFiles_Complaint]
GO

ALTER TABLE [Aduan].[ComplaintFiles] WITH CHECK ADD CONSTRAINT [FK_ComplaintFiles_ComplaintResponse] FOREIGN KEY([ResponseID])
REFERENCES [Aduan].[ComplaintResponse] ([ResponseID])
GO

ALTER TABLE [Aduan].[ComplaintFiles] CHECK CONSTRAINT [FK_ComplaintFiles_ComplaintResponse]
GO

ALTER TABLE [Aduan].[ComplaintFiles] ADD CONSTRAINT [DF_ComplaintFiles_FileType] DEFAULT (N'''C"') FOR [FileType]
GO

Maciej Los 1-Aug-13 4:53am

Everything is clear now. See my answer ;)

2 solutions

Solution 1

Try Something Like this...

SQL

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

SQL

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

Pls Check this Links: Primary Keys -W3 Schools
Primary and Foriegn Keys MSDN

Posted 31-Jul-13 21:03pm

Raja Sekhar S

Updated 31-Jul-13 22:22pm

v5

Comments

Maciej Los 1-Aug-13 3:10am

Raja, i think that OP doesn't understand how identity field works...

Raja Sekhar S 1-Aug-13 3:17am

You may be correct Maciej Los. i will Provide some links for his Learning..

Mare7 1-Aug-13 3:36am

i would really appreciate that. it would really helps me.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-07-31T22:52:00

Have a look at the definition of table:

SQL

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

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 ;)

SQL

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?