General discussion

Locked

SQL Stored Procedure

By Biju Nair G ·
Is it possible to insert multiple records in a talbe using a stored procedure (SQL 6.5 and above)
If yes.. please send me the sample code.

Thanks in advance
Biju Nair

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Stored Procedure

by MtnSqs In reply to SQL Stored Procedure

You simply do multiple insert statements - if you have a source of the data (eg an input text file) then put the insert in a loop reading from the file to set up the variables in each loop.

Collapse -

SQL Stored Procedure

by Biju Nair G In reply to SQL Stored Procedure

need sample code

Collapse -

SQL Stored Procedure

by donq In reply to SQL Stored Procedure

Function PostJournal()
'DEFINE A PROCEDURE TO POST COMPLETED GenJournal RECORDS INTO THE GenLedger TABLE.

'ENABLE PROCEDURAL ERROR HANDLING.
On Error GoTo PostJournal_Err

'DECLARE AND INITIALIZE THE CURRENT DATABASE AS THE DATABASE TO USE.
Dim DB As Database
Set DB = CurrentDb()

DoCmd.Hourglass True

'DECLARE TEMPORARY RECORDSET VARIABLES TO REPRESENT THE VARIOUS RECORDS TO ARCHIVE.
Dim GL As Recordset 'Object Model's "GenLedger" (target) TABLE.
Dim GJ As Recordset 'Object Model's "GenJournal" (source) TABLE.
Dim TransID As Recordset 'PostJournal DRIVER (or Journal records to be posted).

'OPEN THE TARGET RECORDSET USED WITHIN THIS PROCEDURE.
Set GL = DB.OpenRecordset("GenLedger", dbOpenDynaset)'DECLARE PROCEDURE STRING MANAGEMENT VARIABLES.
Dim Sqlstr As String 'SQL Strings.
Dim MBstr As String 'Message Box Strings.

'*****************************************************
'****** Get all completed entries ready to post ******
'*****************************************************
'PUT SQL STATEMENT IN STRING FORM TO LOAD THE PROCEDURE DRIVER BEFORE OPENING THE SOURCE RECORDSET.
Sqlstr = " SELECT * FROM GenJournal WHERE (((GenJournal.gjOAct1) Is Not Null) And ((GenJournal.gjOAmt1) <> 0) And ((GenJournal.gjDAct1) Is Not Null) And ((GenJournal.gjDAmt1) <> 0) And ((GenJournal.gjDAct2) Is Not Null)) ORDER BY GenJournal.gjPK;"
Set TransID = DB.OpenRecordset(Sqlstr, dbOpenDynaset) 'WITH UNRESTRICTED CURSOR MOVEMENT.

'FOR EACH BATCH RECORD ADDED SINCE THE LAST TIME THIS PROCEDURE EXECUTED ADD A NEW "BatchHist" RECORD.
TransID.MoveFirst
While Not TransID.EOF
GL.AddNew 'CREATE A NEW RECORD.

'OPEN THE SOURCE RECORDSET AND INDEX IT TO THE RECORD BEING PROCESSED.
Sqlstr = "SELECT * FROM GenJournal WHERE (((GenJournal.gjOAct1) Is Not Null) And ((GenJournal.gjOAmt1) <> 0) And ((GenJournal.gjDA

Collapse -

SQL Stored Procedure

by donq In reply to SQL Stored Procedure

This was written for Access so you'll need to change any Keywords necessary to address the specific ODBC database you are using.

Collapse -

SQL Stored Procedure

by Biju Nair G In reply to SQL Stored Procedure

Thank again

Collapse -

SQL Stored Procedure

by steven_v_brown In reply to SQL Stored Procedure

Yes, This is one of my simple examples:
If you would like to see more email steven_v_brown@hotmail.com
-----------
Create PROCEDURE sp_AddJobByID
@TargetJob int
AS
-- ******************************************************************
-- Procedure Name: sp_DeleteJobByID
-- Creation Date: 03/06/2001
-- Copyright: Thomas & King, Inc.
-- Written by: Steven V Brown
-- ******************************************************************
-- Description:
-- Adds a specified job from
-- Job, JobStore, Task, TaskStore, JobRecurring tables.
-- ******************************************************************
-- Assumptions: N/A
-- ******************************************************************
-- Input Parameters: @TargetJob
--
-- Output Parameters:
--
-- Return Status: 0 for completion, -1 for error
-- ******************************************************************
-- Amendment History
-- Date Author Purpose
--
-- ******************************************************************
BEGIN
INSERT INTO [BOAPROD].[dbo].[Job]
([iJobID]) values (@TargetJob)
-- ----------------------------------------
INSERT INTO [BOAPROD].[dbo].[JobStore]
([iJobID])values (@TargetJob)
-- ----------------------------------------
INSERT INTO [BOAPROD].[dbo].[Task]
([iJobID]) values (@TargetJob)
-- ----------------------------------------
INSERT INTO [BOAPROD].[dbo].[TaskStore]
([iJobID]) values (@TargetJob)
-- ----------------------------------------
INSERT INTO [BOAPROD].[dbo].[JobRecurring]
([iJobID]) values (@TargetJob)
END
--Error Code
IF @@error <> 0
BEGIN
RAISERROR (50001,16, -1, 'sp_AddJobByID')
RETURN
END
GO

Collapse -

SQL Stored Procedure

by Biju Nair G In reply to SQL Stored Procedure

Poster rated this answer

Collapse -

SQL Stored Procedure

by DGLundman In reply to SQL Stored Procedure

The answer maybe different, depending on the source of the data you are inserting. Also, there is a difference in syntax between Ms SQL 6.0-6.5 and Ms SQL 7.0-8.0 (2000).

Check out the Transaction-SQL Help in the Ms SQL Query Analyzer.

This code is from that help.

--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

--INSERT...EXECUTE('string') example
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like ''8%''
GROUP BY authors.au_id, authors.au_lname
')

Collapse -

SQL Stored Procedure

by Biju Nair G In reply to SQL Stored Procedure

Poster rated this answer

Collapse -

SQL Stored Procedure

by Biju Nair G In reply to SQL Stored Procedure

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums