More Than One Insert Query In Single Store Procedure
Apr 12, 2008I want to write more than one insert query in single store procedure.
or
Is it possible to insert a row into multiple tables with a single insert into query.
Thank You.
ADVERTISEMENT
Store Procedure On Insert
Mar 29, 2001Hello,
I need to write a store procedure that
when someone inserts a blank space("") into all the columns of
a table, the store procedure will set the blank space to
zero (0).
thanks
Store Procedure(insert)
Jan 21, 2008He All
I Ive got this table [Person] And I want to Insert some value on the stored procedure. here's my code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertPerson] 'Davids','davids@gmail.con','David','Scott','21-06-1983','(073) 101 1232','(040) 858 4544','(040) 898 7412',1,'830621 6521 082','Male',3,2,'scotty',332,4,getdate(),getdate(),'Sedick'
(
@varUserName varchar(100),
@varEmailAddress varchar(100),
@varFirstName varchar(256),
@varSurname varchar(256),
@varDateofBirth varchar(100),
@varCellPhoneNumber varchar(20),
@varWorkTelephoneNo varchar(20),
@varHomeNumber varchar(20),
@intIDTypeId int,
@varIDNumber varchar(100),
@varGendervarchar(10),
@intOccupationId int,
@intStatusId int,
@varPassword varchar(100),
@intLoginCount int,
@intLostPasswordCount int,
@varUpdatedBy varchar(256)
)
AS
BEGIN
INSERT INTO [Person]
([UserName],
[EmailAddress],
[FirstName],
[Surname],
[DateofBirth],
[CellPhoneNumber],
[WorkTelephoneNumber],
[HomeTelephoneNumber],
[IDTypeId],
[IDNumber],
[Gender],
[OccupationId],
[StatusId],
[Password],
[LoginCount],
[LostPasswordCount],
[DateCreated],
[DateUpdated],
[UpdatedBy])
VALUES
(@varUserName,
@varEmailAddress,
@varFirstName,
@varSurname,
@varDateofBirth,
@varCellphoneNumber,
@varWorkTelephoneNo,
@varHomeNumber,
@intIDTypeId,
@varIDNumber,
@varGender,
@intOccupationId,
@intStatusId,
@varPassword,
@intLoginCount,
@intLostPasswordCount,
getdate(),
getdate(),
@varUpdatedBy)
END
GO
Wants To Insert Into Multiple Table By A Single Insert Query
Apr 11, 2008Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
Store Procedure Insert Into Function
Aug 10, 2014I have 2 tables
First Table is CUSTOMERS with the columns (CustomerId, CustomerName) Second Table is LICENSES with the columns (LicenseId, Customer) The column customer from the second table is the CustomerId from the First table
I wanted to create a store procedure that insert values into table 2
Insert into Licenses (Customer) Values( Same As CustomerId)
HOW i can get this data from the other table?
How To Insert BLOB Data Using Store Procedure
Apr 2, 2007Hi,Can we insert BLOB data using store procedure using Oracle and ASP.Net.I have inserted BLOB data using insert command, now i want to insert that BLOB via store procedure....any links/tips will be helpful...
View 1 Replies View RelatedInsert Into Table Execute Store Procedure
Mar 26, 2015I have a INSERT INTO where i retunr the result from a store procedure. But I want to only insert the data if the row not already exist. How can i do that? (See Where xxxxxxxxxxxx).
I can't use a function as i store data in a temporary table in the store procedure.
--Get Generated Times
INSERT INTO @GeneratedTimes(
ResourceId ,
DateFrom ,
DateTo )
EXEC dbo.P_GenerateTimes @ApplicationId , @EventId , @FromDate , @ToDate , @WeekScheduleId , @FromTimeToBook , @ToTimeToBook
WHERE xxxxxxxxxxxxxxxxxx
Insert Master/Detail Table Using Store Procedure!!!
Oct 1, 2007now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
View 1 Replies View RelatedInsert Delete Modify In A Single Stored Procedure
Jun 13, 2008hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.
View 1 Replies View RelatedTransact SQL :: Stored Procedure To Update And Insert In Single SP
Jul 17, 2015I have Table Staffsubjects with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] .....
SQL 2012 :: Bulk Insert (or Another Way) To Table From Datatable From Inside Store Procedure
Nov 4, 2014I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedSQL Server 2012 :: Stored Procedure To Update And Insert In Single SP
Jul 17, 2015I have Table Staffsubjects
with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] ....
Single Quote Problem In Insert Query
Oct 31, 2007hi,
how to avoid single quote character in string which comes from textbox in insert query
How To Insert Multiple Images In Single Query
May 7, 2015I want to work upon the concept for the first time. The situation is that I have a hotel table, fields are Hotel_ID, Hotel_Name, Hotel_Pic.
What i want is that when user click on the image of the hotel so a separate form or page is open where all the related images of that hotel are displayed in a table format.
I want to know that how to implement this concept? How many tables i would be needing for this.
Store Procedure Query
Feb 18, 2006Hello AllI get stuck in one problem , Please help me and excuse me on the poorKnowledgein SQL Server Store procedureQ1...I want to return a string type value from store procedure , Is itpossible to do it.if posible then Please guide us how we can do it .Q2...I search a lot on net but i am unable to understand the ExtendedStore Procedure.How to register it and how to use in our store procedure .Q3 ... How to use xp_sendmail.dll in SQL Server.Thanking youWith regardsTarun
View 1 Replies View RelatedSelect Query Vs Store Procedure Query
Oct 29, 1998hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
Q:Keywords Query In Store Procedure
Nov 6, 2003--keywords table:
CREATE TABLE #KeyWord
(
[QueryId] int IDENTITY (1, 1) NOT NULL ,
[QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND)
[SKey] nvarchar (200) NULL
)
ON [PRIMARY]
--target table:
CREATE TABLE Test
(
[PId] int IDENTITY (1, 1) NOT NULL ,
[PTitle] nvarchar (200) NULL
)
ON [PRIMARY]
================
I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
PS. sqlserver's full text index mode is off.
How We Can Insert Multiple Query With Transaction Roll Bck For A Single Record
Apr 11, 2008Hello,
I have problem for insert multiple query for insert in differenr tabels for a single record.
I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport,
i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then...........
so plz help me
Thanks
Dynamic Query With Variables In Store Procedure
May 18, 1999Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
Thank for any help in advance
Hank Lee
How To Dynamically Assign Database Name In Query Or Store Procedure?
Sep 22, 2006Hello,
I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?
something like
Declare @MyDatabaseName varchar(30)
set @MyDatabaseName = "MyDB"
SELECT * from MyDatabaseName.dbo.MyTable
Any suggestion? Please.
Thanks in advance
SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options
May 4, 2015What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?
Jun 17, 2015Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
To Store Data With Single Quote
Mar 10, 2008HI,I anm geting error when i want to store some text which contens single quote like this Hi I am 'santosh'.as i am using text editor which genetates XML data (not pure) so i have used varchar(max) to store the data but it gives error.
Is thier any way to store text with single quote........
urgent plz.
Store Multiple Values In A Single Value
Nov 6, 2005was hoping someone couild provide some insight into a problem I'm trying to solve.
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:
CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32
A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.
Has anyone done anything like this before?
If so, any help would be appreciated!
Thanks in advance!
Transact SQL :: Table To Store All Transaction Happened On A Single Day
Jun 11, 2015I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this
My I want to show this result like this
ARS
BNT 0 0
BCN 0 0
SCN 1 12
BTC 0 0
STC 0 0
SNT 0 0
[code]...
and so on for all the the currency lists.how can I achieve like this .
Call Store Procedure From Another Store Procedure
Nov 13, 2006 I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Stored Procedure To Run Query And Then Insert
Oct 11, 2006I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.
Insert Query With Stored Procedure
Jul 20, 2005Hi,is it possible to create an "INSERT INTO ..... "Select from storedprocedure" Query?I want to create an temporary table. In this table I want to enter the data,which I can get from an stored procedure.But in the FROM-clause a stored procedure is not allowed?
View 1 Replies View RelatedSTORED PROCEDURE INSERT QUERY ADVICE
Jun 22, 2006I have 3 tables:
1) users: users information
2) products: products available
3) purchases: has relationships to both the users and products tables.
How can I write a stored procedure that inserts to the Purchases table and
populates to the other two tables. I was thinking that the records have to be
populated first on the "users" and "products" table then
when my INSERT is done on the "purchases" table it will refer to the
other two tables. I tried query builder but it wouldn’t allow me to insert from
one single INSERT statement to one table. Any advice that on how to approach
this? Thanks you!
Want 2 Store Multiple Room Numbers Of A Hotel In A Single Column Using SQL Server2k.
Jan 15, 2004Hi!
Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.
Can anybody tell me how to do it??
Thanx.
Single Complex INSERT Or INSERT Plus UPDATE
Jul 23, 2005Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies View RelatedIntegration Services :: Store Multiple Config In Single Table Using SSIS Configurations?
Oct 20, 2015I want to store multiple config files into single table I am unable to do it.
View 8 Replies View RelatedStored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
May 14, 2008Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.