October 20, 2008 at 5:04 pm
I have the following issue:
I have customerID field saved into a #Temp table. There are 128 records in this temp table.
I need to take these 128 records and insert a record into an Address table and then the take the scope_identity value and insert into the Customer_Address table along with the customer id.
Manually the process looks something like this:
INSERT INTO ADDRESS DEFAULT VALUES
Select scope_identity()
INSERT INTO Customer_ADDress( CustOmer_ID, Customer_Address_iD,history_type_id)
Values (20992670,21094437,1)
Where the customer_id (from the #temp table) and Customer_Address_ID (Indentity value from the Address table) value is being manually entered everytime. How can I do this all at once for all the 128 records in the #temp table?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 20, 2008 at 6:13 pm
What if 2 customers share the same address?
How do you manage it?
_____________
Code for TallyGenerator
October 21, 2008 at 6:10 am
They won't.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 22, 2008 at 9:13 am
Look up the OUTPUT clause in B.O.L. - that should work for you..
Mark Starr
Mark
Just a cog in the wheel.
October 22, 2008 at 10:01 am
Arthur.Lorenzini (10/21/2008)
They won't.
Then you have 1 to 1 relation between Customers and Addresses, so you may just repeat CustomerID as AddressID.
_____________
Code for TallyGenerator
December 9, 2010 at 5:21 pm
It amazes me that questions get asked, and yet no-one answers the questions. I would find so useful the example asked for here.
Leave the error checking aside, I too an trying to understand how to do an insert and grab the identity from a previous table.
December 10, 2010 at 1:28 pm
vbradham (12/9/2010)
It amazes me that questions get asked, and yet no-one answers the questions. I would find so useful the example asked for here.Leave the error checking aside, I too an trying to understand how to do an insert and grab the identity from a previous table.
I hear ya. How dare Mark suggest the OP put some effort into it by searching BOL? It's unthinkable.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 10, 2010 at 1:42 pm
vbradham (12/9/2010)
It amazes me that questions get asked, and yet no-one answers the questions. I would find so useful the example asked for here.Leave the error checking aside, I too an trying to understand how to do an insert and grab the identity from a previous table.
the problem for me is the usual: not enough information.
to give aproper, testable code example of the OUTPUT clasue, the actual table definitiosn are required. no specifics means you get general, non specific answers.
here is a full example of how to use an output clause , but i do not know if the OP can adapt it to fill his needs; his examples were very homeworkish, which also gives poeple a reason to hesitate to post fully workable answers...we don't like doing peoples homework, since noone benefits from it.
CREATE TABLE adds(
adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
code VARCHAR(30) )
DECLARE @MyResults TABLE(
ID int,
newcode VARCHAR(30),
oldcode VARCHAR(30) )
Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )
INSERT INTO adds(code)
OUTPUT
INSERTED.adid,
INSERTED.code,
NULL
INTO @MyResults
SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL
SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL
SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL
SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL
SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL
SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL
SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL
SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL
SELECT 'cadetblue'
declare @MyResults TABLE(ID int,newcode varchar(30),oldcode varchar(30) )
UPDATE dbo.adds
SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )
OUTPUT
INSERTED.adid,
INSERTED.code,
DELETED.code
INTO @MyResults
WHERE LEFT(code,1) = 'a'
SELECT * FROM @MyResults
/*--results of update
ID newcode oldcode
1 ALICEB aliceblue
2 ANTIQUEWH antiquewhite
3 AQ aqua*
4 AQ aqua*
5 AQUAMAR aquamarine
6 AZ azure
*/
Lowell
December 10, 2010 at 2:10 pm
vbradham (12/9/2010)
It amazes me that questions get asked, and yet no-one answers the questions. I would find so useful the example asked for here.Leave the error checking aside, I too an trying to understand how to do an insert and grab the identity from a previous table.
The question, as asked, was answered.
Here's an example, based on what was asked:
Insert into Table
output inserted.ID into AnotherTable
select from SourceTable
However, that example doesn't really give you anything you can use, compared to what's in BOL/MSDN:
Examples
--------------------------------------------------------------------------------
A. Using OUTPUT INTO with a simple INSERT statement
The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar table variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. However, note that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the column inserted.ScrapReasonID.
USE AdventureWorks2008R2;GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
That's just ONE of the examples from BOL/MSDN. Lots more useful than a scrap of a sample based on the original post. Plus, the article on MSDN lays out all the rules about using Output, the syntax for it, suggestions on using it, and limitations on the tables it can be used to insert into.
That's why the tendency is to suggest, "Take a look at Output in BOL", instead of trying to write several pages of data that Microsoft already wrote for you.
Please also note that searching Bing or Google for "t-sql output" finds the article as the top result, so it's not even hard to find, once someone has suggested that what you need to look for is "output".
Yes, it would save you a few seconds for someone to rewrite all the material Microsoft has written on it. But we're all here on our own time, not being paid or anything for answering here, so instead of demanding that someone else spends hours, please respect it when someone suggests you spend a few seconds (that's as long as it takes to type that into Google and hit Enter).
So, yes, you're amazed that someone isn't willing to spend a huge amount of unpaid time helping you, but I'm equally amazed that you demand that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply