January 18, 2012 at 7:08 am
I have a spreadsheet of companies that I need to insert into our CRM. Basically the CompanyID, CompanyName and Address. I have to insert the new records into two tables. Organization Master and Organization Address. And to make it even more exciting, I have to get the new ID for the organization and create a reference table showing that CompanyID 123 = OrganizationID 987.
CompanyID and OrganizationID are both integers.
So first I would need to insert the company name into the Company master. That would create the master record that I would need to create an address record. That would also create an OrganizationID based off of an Identity field. I need to return that Organization ID and associate it with my original data and then take the address and create an address record using the new OrganizationID.
I think I can handle the second half of the problem of inserting the address, but only once I have the OrganizationID returned and associated with the old company ID.
Now to try and simplify it:
I have a data sheet with
CompanyID CompanyName
I need to insert it into the OrganizationMaster table, but I can only insert the name. It will assign an OrganizationID.
OrganizationID CompanyName
I need that OrganizationID returned to me and associated with the CompanyID.
OrganizationID CompanyID
I am not necessarily asking anyone to write me anything. What I am looking for is a direction to look for research. Thanks
Adam
CREATE TABLE dbo.[CompanyRecords] (
[CompanyID] int NOT NULL,
[CompanyName] varchar(75),
[Address1] varchar(64),
[Address2] varchar(64),
[City] varchar(28),
[State] varchar(2),
[Zip] varchar(16)
)
GO
CREATE TABLE dbo.[OrganizationRecords] (
[Organization_ID] int IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[Organization_Name] varchar(75),
)
GO
CREATE TABLE dbo.[OrganizationAddress] (
[OrganizationAddressID] int IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[OrganizationID] int NOT NULL,
[AddressLine1] varchar(64),
[AddressLine2] varchar(64),
[City] varchar(28),
[State] varchar(2),
[Zip] varchar(16))
GO
Insert Into dbo.[CompanyRecords]
VALUES
('124613','ABC Children Center','1234 Lark Ln','','Sacramento','CA','95825'),
('124614','DEF Valley School','567 Park Dr', '', 'Sacramento','CA','95864'),
('124651','GHI Elementary School','8900 W Fair Rd','','West Sacramento','CA','95691')
GO
January 18, 2012 at 7:56 am
Take a look at the Output clause. http://msdn.microsoft.com/en-us/library/ms177564.aspx
This would be a great starting point for doing exactly what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 8:16 am
Thanks. Briefly looking at it, I think it might be what I need. I have also been reading about Ident_Current, Scope_Identity and @@Identity. Output is looking like a better option though.
Adam
January 18, 2012 at 8:18 am
The others will work but the Output clause is a bit more flexible and is more of the preferred way. Ping back if you need some help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 8:44 am
The OUTPUT clause is going to work only with the MERGE statement.
CREATE-- DROP -- TRUNCATE
TABLECompOrgReference
(
[CompanyID]INTEGERNOT NULL,
[Organization_ID]INTEGERNOT NULL
)
MERGEOrganizationRecords u
USING(
SELECTr.Organization_ID, c.CompanyID, c.CompanyName
FROMCompanyRecords c
LEFT JOIN CompOrgReference r
ONr.CompanyID = c.CompanyID
WHEREr.CompanyID IS NULL
) x
ONx.Organization_ID = u.Organization_ID
WHENNOT MATCHED THEN
INSERT (Organization_Name)
VALUES (x.CompanyName)
OUTPUTx.CompanyID, INSERTED.Organization_ID
INTOCompOrgReference(CompanyID, Organization_ID)
;
January 18, 2012 at 9:21 am
fahey.jonathan (1/18/2012)
The OUTPUT clause is going to work only with the MERGE statement.
Huh???
Output works with any of the data modification actions. Not just Merge.
- 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
January 18, 2012 at 9:22 am
fahey.jonathan (1/18/2012)
The OUTPUT clause is going to work only with the MERGE statement.
From BOL: "Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view."
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 9:26 am
On the original question, definitely go with an Output clause.
If you insert all the companies at one go, and use Output Into to dump the names and their IDs into a temp table, you can then use that temp table in Insert Select statements, joining it to the addresses, et al.
You're inserting into three target tables, correct? If you use this method, of inserting all at once and Output Into, you can do the whole thing in four steps:
1. Define the temp table
2. Insert all companies
3. Insert all organizations
4. Insert all addresses
If you need the organization IDs in order to insert the addresses, then it's 5 steps, since you'll need to define a temp table for that too.
If you do it using Scope_Identity or any of the other single-value methods, you'll have to do four steps PER company, instead of four steps total, plus the overhead steps of defining a cursor of one sort or another. It's a lot more work, and usually takes longer to run too.
- 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
January 18, 2012 at 9:42 am
We are not trying to do an UPDATE or a DELETE, so using the OUTPUT clause with those statements does not help.
We want to get the value of a column not in the INSERTED or DELETED tables. The OUTPUT clause on the INSERT statement does not currently allow that, to my knowledge. From BOL:
from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.
Therefore, the MERGE clause of the INSERT statement cannot be used because we need the value of the CompanyID which is not in the INSERTED or DELETED tables.
The only other statement that allows an OUTPUT clause is the MERGE statement. What I meant was that we can only use the OUTPUT clause of the MERGE statement in this case because we are not doing an UPDATE or DELETE and the INSERT does not give us access to the column we need.
Sorry for the confusion.
January 18, 2012 at 10:16 am
The Merge and Output worked like a charm.
Thanks
January 18, 2012 at 10:25 am
fahey.jonathan (1/18/2012)
We are not trying to do an UPDATE or a DELETE, so using the OUTPUT clause with those statements does not help.We want to get the value of a column not in the INSERTED or DELETED tables. The OUTPUT clause on the INSERT statement does not currently allow that, to my knowledge. From BOL:
from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.
Therefore, the MERGE clause of the INSERT statement cannot be used because we need the value of the CompanyID which is not in the INSERTED or DELETED tables.
The only other statement that allows an OUTPUT clause is the MERGE statement. What I meant was that we can only use the OUTPUT clause of the MERGE statement in this case because we are not doing an UPDATE or DELETE and the INSERT does not give us access to the column we need.
Sorry for the confusion.
How can you get a value from an insert that isn't in the inserted table? If you insert it, it is in the inserted table. The inserted table IS the insert (including an identity values).
Unless I am missing something this should show you.
create table #OutputTest
(
ID int identity,
SomeValue varchar(10)
)
declare @MyTable table(ID int, SomeValue varchar(10))
insert #OutputTest
output inserted.ID, inserted.SomeValue into @MyTable
values('Value Here'),('Another'),('Again')
select * from @MyTable
drop table #OutputTest
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 10:25 am
adams.squared (1/18/2012)
The Merge and Output worked like a charm.Thanks
Glad that worked you and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 11:26 am
Edit: Misread what fahey was trying to do. Never mind.
- 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
January 18, 2012 at 1:10 pm
Sean Lange (1/18/2012)
How can you get a value from an insert that isn't in the inserted table? If you insert it, it is in the inserted table. The inserted table IS the insert (including an identity values).
In the original problem, the user had OldID and Name in OldTable. He wanted to insert the Name into NewTable that assigned a NewID but did not contain an OldID field. He wanted to have a cross-reference of OldID and NewID.
INSERT INTO NewTable (Name) SELECT Name FROM OldTable
Using an OUTPUT clause on that INSERT will give us NewID and Name only, not OldID because we can only reference INSERTED and DELETED, and OldID was not an inserted field.
Using the MERGE statement, we have access to the "from_table_name" syntax, meaning we can get OldTable.OldID and INSERTED.NewID in the same OUTPUT statement, giving us the desired cross-reference.
January 18, 2012 at 1:14 pm
Maybe I am misreading the OP but there was never any mention of anything other than inserts. Either way the OP has figured out the solution. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply