TH3M4D0N3
asked on
MSSQL Insert with multiple selects Query
I have a table sitePersonnel that i am splitting up and inserting into several other tables: persons, assistants, emails, addresses, and site_serviceContacts. I have the outline of what i want to do, but now i need to put it all into a MSSQL script, i could write this in other languages however i need to know how to do it in SQL I have an idea but i dont know how to call multiple selects inside of an insert statement which is the only way i can think of to do this at the moment. Any input on how to properly copy the database to several different tables all connected by SID using the first insert from the persons table (which assigns the SID automagically) Thanks for your help.
I can change any variable types if you place code in here for help, and work on examples, i have a very good understanding of programming, just not much in sql, especially syntax and allowable variable types, such as can i use a cursor and populate a variable like an array? things like that which would allow me to write this on my own.
The table split order is as follows:
FROM SitePersonnel.Table (SB) format is:
SB Field -> table.sometimes differing fieldname is after . (ie assistants table)
- LastName -> persons
- FirstName -> persons
- MiddleName -> persons
- Birthday -> persons
Persons.Table generates SID which is the main index on all the tables.
-persons.SID -> addresses
-AddressLine1 -> addresses
-AddressLine2 -> addresses
-City -> addresses
-State -> addresses
-Country -> addresses
-PostalCode -> addresses
-Phone -> addresses
-PhoneExt -> addresses
-Fax -> addresses
-JobTitle -> addresses
-persons.SID -> assistants
-AssistantName -> assistants.FirstName -Split AssistantName String 1st half
-AssistantName -> assistants.LastName -Split AssistantName String 2nd half
-AssistantEmail -> assistants
-AssistantPhone -> assistants
-persons.SID -> emails
-EmailAddress -> emails
-persons.SID -> site_ServiceContacts
-siteID -> site_ServiceContacts
-category -> site_ServiceContacts
I can change any variable types if you place code in here for help, and work on examples, i have a very good understanding of programming, just not much in sql, especially syntax and allowable variable types, such as can i use a cursor and populate a variable like an array? things like that which would allow me to write this on my own.
The table split order is as follows:
FROM SitePersonnel.Table (SB) format is:
SB Field -> table.sometimes differing fieldname is after . (ie assistants table)
- LastName -> persons
- FirstName -> persons
- MiddleName -> persons
- Birthday -> persons
Persons.Table generates SID which is the main index on all the tables.
-persons.SID -> addresses
-AddressLine1 -> addresses
-AddressLine2 -> addresses
-City -> addresses
-State -> addresses
-Country -> addresses
-PostalCode -> addresses
-Phone -> addresses
-PhoneExt -> addresses
-Fax -> addresses
-JobTitle -> addresses
-persons.SID -> assistants
-AssistantName -> assistants.FirstName -Split AssistantName String 1st half
-AssistantName -> assistants.LastName -Split AssistantName String 2nd half
-AssistantEmail -> assistants
-AssistantPhone -> assistants
-persons.SID -> emails
-EmailAddress -> emails
-persons.SID -> site_ServiceContacts
-siteID -> site_ServiceContacts
-category -> site_ServiceContacts
Actually you have to split this in multiple insert statements -- one for each of the tables you are splitting to.
ASKER
Yeah I know this is what I have so far
DECLARE @PersonnelDataFN nvarchar(1024),@PersonnelDataLN nvarchar(1024),@PersonnelDataMN nvarchar(1024),@PersonnelDataBD nvarchar(1024), @PersonsData nvarchar(1024), @AssistantsData nvarchar(1024), @AddressData nvarchar(1024), @EmailData nvarchar(1024), @TempStorage nvarchar(1024)
DECLARE cursor_PersonnelToPersons CURSOR
FOR SELECT LastName, FirstName, MiddleName, Birthday FROM SitePersonnel;
OPEN cursor_PersonnelToPersons
FETCH NEXT FROM cursor_PersonnelToPersons INTO @PersonnelDataFN, @PersonnelDataLN, @PersonnelDataMN, @PersonnelDataBD;
WHILE @@FETCH_STATUS = 0
BEGIN
set @PersonsData = 'INSERT INTO Persons (LastName, Firstname, MiddleName, Birthday ) ' + @PersonnelDataLN + ', ' + @PersonnelDataFN + ', ' + @PersonnelDataMN + ', ' + @PersonnelDataBD + '';
set @AssistantsData = 'INSERT INTO Assistants (SID, AssistantPrimary, AssistantLastName, AssistantFirstName, AssistantEmail, AssistantPhone) ' + @TempStorage +', 1, select
left( AssistantName, charindex( ' ', AssistantName ) -1 ) as FirstName
, right( AssistantName, len( AssistantName ) - charindex( ' ', AssistantName )) as LastName
from SitePersonnel';
set @AddressData = 'INSERT INTO Addresses (SID, AddressPrimary, AddressLine1, AddressLine2, City, State, Country, PostalCode, Phone, PhoneExt, Fax, JobTitle) ' + @TempStorage + ', 1, ' + @PersonnelData.AddressLine1 + ', ' + @PersonnelData.AddressLine2 + ', ' + @PersonnelData.City + ', ' + @PersonnelData.State + ', ' + @PersonnelData.Country + ', ' + @PersonnelData.PostalCode + ', ' + @PersonnelData.Phone + ', ' + @PersonnelData.PhoneExt + ', ' + @PersonnelData.Fax + ', ' + @PersonnelData.JobTitle + '';
set @EmailData = 'INSERT INTO Emails (SID, EmailPrimary, EmailAddress) ' + @TempStorage + ', 1, ' + @PersonnelData.EmailAddress + '';
BEGIN TRANSACTION
BEGIN TRY
exec sp_executesql @PersonsData
set @TempStorage = SCOPE_IDENTITY;
exec sp_executesql @AssistantsData
exec sp_executesql @AddressData
exec sp_executesql @EmailData
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
FETCH NEXT FROM cursor_PersonnelToPersons INTO @PersonnelData;
END
CLOSE cursor_PersonnelToPersons;
DEALLOCATE GLOBAL cursor_PersonnelToPersons;
GO
ASKER
Though i dont know how to do the assistant data section? any ideas, and for overall would this work well? or is there a better approach? i tend to overkill when simpler ways exist.
If these tables don't already exist, you can do this:
SELECT col1, col2, col3
INTO persons
FROM SitePersonnel
SELECT col1, col4, col5, col6 As newCol
INTO assistants
FROM SitePersonnel
... And so on for emails, addresses, and site_serviceContacts
If these tables have already been created, same concept just using:
INSERT INTO persons(col1, col2, col3)
SELECT col1, col2, col3
FROM SitePersonnel
Regards,
Kevin
SELECT col1, col2, col3
INTO persons
FROM SitePersonnel
SELECT col1, col4, col5, col6 As newCol
INTO assistants
FROM SitePersonnel
... And so on for emails, addresses, and site_serviceContacts
If these tables have already been created, same concept just using:
INSERT INTO persons(col1, col2, col3)
SELECT col1, col2, col3
FROM SitePersonnel
Regards,
Kevin
ASKER
Thanks Kevin, maybe what i put up while you were typing will help clear up what i am trying to do.
Ah, I see because you are generating a new PK in the process. Sorry for the posts on simple syntax...With statement that you would do in one insert I went down the wrong path.
You would have to do this as you think by doing the insert of fields to persons table and then using something like scope_identity() to get the generated key for the next inserts.
However, my first thought is to create column on your existing table and prepopulate it with key using something like newid() and then when you insert into each table just have that column be the common one in each select statement.
You would have to do this as you think by doing the insert of fields to persons table and then using something like scope_identity() to get the generated key for the next inserts.
However, my first thought is to create column on your existing table and prepopulate it with key using something like newid() and then when you insert into each table just have that column be the common one in each select statement.
ASKER
How would i do something like that?
I lost you a bit at the last paragraph, what table would you insert the new field into? why? is the current syntax correct should i use a cursor? how do i split the assistantName into firstname lastname?
there are several areas still unanswered, thank you for your input so far though Kevin,
though i am holding on accepting anything until i get all my answers.
I lost you a bit at the last paragraph, what table would you insert the new field into? why? is the current syntax correct should i use a cursor? how do i split the assistantName into firstname lastname?
there are several areas still unanswered, thank you for your input so far though Kevin,
though i am holding on accepting anything until i get all my answers.
Sorry, sometimes thoughts rattle around in my brain and they come out they way I am thinking them. :)
Firstly, I was suggesting a simplier approach since you want all the data from one record to end up referencing the same primary/foreign key SID. I would modify structure of sitePersonnel, adding a column called SID and make it a uniqueidentifier field for example. Using an update statement, set value of this new column for all records using newid().
e.g.
UPDATE sitePersonnel
SET SID = newid()
WHERE SID IS NULL
Now you have SID for each row and can simply do what showed earlier where you have separate insert statements, each with SID in select portion. For tables you want this to have a different name, just alias appropriately if using SELECT INTO.
Secondly, on use of cursor, that should work out fine although it looks like you don't quite have this syntax in your dynamic query correct:
INSERT INTO tablename(col1, col2, col3) VALUES(@val1,@val2,@val3) -- think missing values above
For the subsquent insert, you capture scope_identity (although believe it is scope_identity()) but then you are selecting columns directly from sitepersonnel table with no filter so you will insert all the records with same identity value. If you are going to use the cursor, you would want to get all the values of the current record into variables and use those to construct your statements.
If you are going to use this approach, I would suggest not using the dynamic SQL pieces so you can see whether or not you have a proper formed SQL statement for each piece. Maybe I am wrong, but doesn't appear that you need to do dynamic SQL here.
Thirdly, from what I saw, you are on right path for splitting the name as long as it is always in the format:
"FirstName LastName"
The SQL would be something like:
FirstName = LEFT(AssistantName, charindex( ' ', AssistantName ) -1)
LastName = RIGHT(AssistantName, charindex(' ', Reverse(AssistantName)) - 1) -- or len() - charindex()
Hopefully I covered everything.
Firstly, I was suggesting a simplier approach since you want all the data from one record to end up referencing the same primary/foreign key SID. I would modify structure of sitePersonnel, adding a column called SID and make it a uniqueidentifier field for example. Using an update statement, set value of this new column for all records using newid().
e.g.
UPDATE sitePersonnel
SET SID = newid()
WHERE SID IS NULL
Now you have SID for each row and can simply do what showed earlier where you have separate insert statements, each with SID in select portion. For tables you want this to have a different name, just alias appropriately if using SELECT INTO.
Secondly, on use of cursor, that should work out fine although it looks like you don't quite have this syntax in your dynamic query correct:
INSERT INTO tablename(col1, col2, col3) VALUES(@val1,@val2,@val3) -- think missing values above
For the subsquent insert, you capture scope_identity (although believe it is scope_identity()) but then you are selecting columns directly from sitepersonnel table with no filter so you will insert all the records with same identity value. If you are going to use the cursor, you would want to get all the values of the current record into variables and use those to construct your statements.
If you are going to use this approach, I would suggest not using the dynamic SQL pieces so you can see whether or not you have a proper formed SQL statement for each piece. Maybe I am wrong, but doesn't appear that you need to do dynamic SQL here.
Thirdly, from what I saw, you are on right path for splitting the name as long as it is always in the format:
"FirstName LastName"
The SQL would be something like:
FirstName = LEFT(AssistantName, charindex( ' ', AssistantName ) -1)
LastName = RIGHT(AssistantName, charindex(' ', Reverse(AssistantName)) - 1) -- or len() - charindex()
Hopefully I covered everything.
ASKER
How would i put the FirstName and Lastname into the query?
Its part of the original question, how do i take
FirstName = LEFT(AssistantName, charindex( ' ', AssistantName ) -1)
LastName = RIGHT(AssistantName, charindex(' ', Reverse(AssistantName)) - 1) -- or len() - charindex()
and insert it into Assistants table? within the same query? or do i need to run firstname, lastname in a seperate query somehow?
Its part of the original question, how do i take
FirstName = LEFT(AssistantName, charindex( ' ', AssistantName ) -1)
LastName = RIGHT(AssistantName, charindex(' ', Reverse(AssistantName)) - 1) -- or len() - charindex()
and insert it into Assistants table? within the same query? or do i need to run firstname, lastname in a seperate query somehow?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry for the long response by the way, I am on a business trip and so just getting through e-mail.
ASKER
I figured it out, but no biggie, you helped and thats what matters. i dont know how they expect this place to work without paying you guys or something more incentive based. you were the only one to answer my question, though i dont know if i asked it improperly or if it was just that its a hard question/easy question.