March 12, 2010 at 7:35 am
Hello,
Im hoping someone can help me with this one ill be as understanding as possible.
Ok i have a Database called Record (dnt ask why its just a practise one) within the database i have 3 tables.
Table one = Address, 2nd= ContactDetails, 3rd= EmployerCompanyName.
I need to create a Stored procedure that will take information from one table within the database and populate it in to one of the remaining tables and also creating a new column for that information to go in,
I have opened up a new Stored Procedure and from what im aware this is just a basic template from SQL Server 2005.
-----------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
------------------------------------------------------------------------------------------
If im totally honest with you im pretty sure i will not need half the information that has been added above in that script. what im asking for if someone can write out a script for me that would generate the situation i have mentioned above.
If someone can do that also just a lilttle paragraph stating what they have done and how the script will work it would be much appreciated 🙂
March 12, 2010 at 8:44 am
scott this looks an aweful lot like homework; we volunteer our time here to help you learn and understand concepts, so lets make sure we help you the right way.
inserting into another table means you have to know the destination table structure, and how the other tables are related.
show us the CREATE TABLE statements for your three tables. then explain what values from what table need to go into the other table. we need actual, working CREATE TABLE statements that we can copy and paste in order to follow along.
show us what you've tried so far, even if it doesn't work. After that, we'd have everything we need to help you.
simple example of what you should provide:
CREATE TABLE Address
AddressId INT
Address1 )
CREATE TABLE ContactDetails...
Lowell
March 12, 2010 at 8:58 am
Hello, thank you for the reply, you will see below 3 different statements i have written above what statements goes with what tables.
Firstly the Address Table
CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[CompanyStreet] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CompanyTown] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CompanyCounty] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CompanyPostcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[ContactStreet] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[ContactTown] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[ContactPostcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[EmployerID] [int] NULL,
[ContactID] [int] NULL,
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Record]
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address_ContactDetails] FOREIGN KEY([ContactID])
REFERENCES [dbo].[ContactDetails] ([ContactID])
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address_EmployerCompanyName] FOREIGN KEY([EmployerID])
REFERENCES [dbo].[EmployerCompanyName] ([EmployerID])
2nd ContactDetails
CREATE TABLE [dbo].[ContactDetails](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[ContactName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[AddressID] [int] NULL,
[EmployerID] [int] NULL,
[FirstName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[LastName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_ContactDetails] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Record]
GO
ALTER TABLE [dbo].[ContactDetails] WITH CHECK ADD CONSTRAINT [FK_ContactDetails_EmployerCompanyName] FOREIGN KEY([EmployerID])
REFERENCES [dbo].[EmployerCompanyName] ([EmployerID])
3rd EmployerCompanyName
CREATE TABLE [dbo].[EmployerCompanyName](
[EmployerID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_EmployerCompanyName] PRIMARY KEY CLUSTERED
(
[EmployerID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Please let me know if you need anything else.
Thanks in Advance.
March 12, 2010 at 9:05 am
great scott!
the structure really helps us understand the relationships of the data.
now to the meat and potatoes of the issue;
you said this:
...take information from one table within the database and populate it in to one of the remaining tables and also creating a new column for that information to go in,
as usual, the devil is in the details...Which table are you talking about there? is there a fourth table that has the details that would be appropriate to chop into the three tables you pasted here? is that your objective?
Lowell
March 12, 2010 at 9:27 am
Hi Lowell,
Ok ill try and explain this as easy as possible, i havent been informed on what information im taking from what table yet, but what i was going to ask if someone or your self, could create a Skelton Script on where the relevant information is missing for example, SELECT ....... FROM ....... so when i get told what needs to be done i can go through the script and fill out the relevant information.
Regarding transferring information from one table to another i mean....say im looking at my dbo.Address Table and i want to Subtract The ContactPostcode Column and transfer it to the dbo.ContactDetails table but before i can insert the information in to the dbo.ContactDetails Table i need to create a new Column for the information to go in, im aware i can do this by modifying the Table it self but what i would and have been asked for is to create a script that can create a new column within a Existing Table, subtract information from one table and transfer it in to the new column which is in a different table.
And to answer your other question i have 3 Tables.
Thanks again its much appreciated.
March 12, 2010 at 9:41 am
ok here's my assumption based on what you posted: the plan is to move the "Contact"'s address info into the Contact Table, instead of having it as a seperate reference table.
If that's not right, ignore the rest of this post and clarify.
first, add the relevant columns to the contact table :
--copy the columns to the new table
ALTER TABLE [dbo].[ContactDetails] ADD [CompanyStreet] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [CompanyTown] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [CompanyCounty] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [CompanyPostcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [ContactStreet] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [ContactTown] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[ContactDetails] ADD [ContactPostcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
--migrate the existing data.
UPDATE [ContactDetails]
SET [ContactDetails].[CompanyStreet] = [Address].[CompanyStreet]
[ContactDetails].[CompanyTown] = [Address].[CompanyTown],
[ContactDetails].[CompanyCounty] = [Address].[CompanyCounty],
[ContactDetails].[CompanyPostcode] = [Address].[CompanyPostcode],
[ContactDetails].[ContactStreet] = [Address].[ContactStreet],
[ContactDetails].[ContactTown] = [Address].[ContactTown],
[ContactDetails].[ContactPostcode] = [Address].[ContactPostcode]
FROM [Address]
WHERE [ContactDetails].[AddressID] = [Address].[AddressID]
--since the data is in the table now, we do not need the column or the foreign key
ALTER TABLE [dbo].[ContactDetails] DROP COLUMN [AddressID]
the migration of the address to the Company would be identical...just swap out the table name from the code above.
Lowell
March 12, 2010 at 1:26 pm
Hi Lowell,
Thats brilliant thank you 🙂 quick question are CURSOR easy to use? as i have looked at a few examples i.e one for backing up a Database and if im honest i got totally lost........what i want to do is create a script that can update some information in my Tables, whilst obaying the Relationships betweem them.....im aware you have to start with the keyword DECLARE to create a Cursor but im not 100% after that.
what i would like to do is create a CURSOR and update another record, i.e change of address in a different table by running a script.....
Hope you can help.
Thanks in advance.
March 12, 2010 at 3:01 pm
Hi Lowell,
Regarding what i have just wrote, im trying to piece together a Cursor that allows me to retrieve information from one Table and update another table below you will see how far i have got with the script.
USE Record
DECLARE up_date CURSOR
FOR
SELECT ContactStreet FROM Address ORDER BY AddressID
OPEN up_date
FETCH NEXT FROM up_date
now im stuck 🙁
March 12, 2010 at 3:27 pm
scott.atkins (3/12/2010)
Hi Lowell,Regarding what i have just wrote, im trying to piece together a Cursor that allows me to retrieve information from one Table and update another table below you will see how far i have got with the script.
USE Record
DECLARE up_date CURSOR
FOR
SELECT ContactStreet FROM Address ORDER BY AddressID
OPEN up_date
FETCH NEXT FROM up_date
now im stuck 🙁
Usually, cursors can be avoided. They are not bad or evil by definition. But since a cursor will process one row at a time, it usually does not perform well, especially when talking about large amount of data sets.
Let's assume the following scenario (not really related to programming though...)
Your task is to get a six pack of "Whatever-You-Like" from your car into the fridge of your appartement in the 4th floor during Super Bowl. Start scenario: 6 bottles in your car. Goal: 6 bottles in your fridge. Task to perform with each bottle: walk down to your car, grab the bottle, take it 4 floors up, put it in the fridge.
Option 1: get each bottle separately.
Option 2: get the six pack instead of one bottle.
Think about what you missed from Super Bowl for each option.
Back to programming T-SQL: The each-bottle-at-once approach is called CURSOR. 😀
Instead of processing row by row you should do it all in one:
UPDATE targetTable
Set Col=value
FROM SourceTable
INNER JOIN TargetTable ON JoinCondition.
The big advantage: you have plenty of time to watch the game - errrmmm the server is not as busy as it would be with carrying one bottle/row at a time. 😉
March 12, 2010 at 3:38 pm
Hi,
Thanks for the explanation ha ha! Very good,
Right back to SQL.
So in a way I can do one at a time but obviously it will take longer,
So regarding the script I had written what do I have to change to make it select everything and transfer it to another Table, I do Apologize if the Answer is staring me straight in the face, but this is the first time i have ever looked or going to be using a cursor...so as much information you can provide would be highly appreciated.
Thanks in Advance.
March 12, 2010 at 4:22 pm
Like I wrote before:
UPDATE targetTable
Set Col=value
FROM SourceTable
INNER JOIN TargetTable ON JoinCondition.
You don't need to go for each bottle separately... 😉
So, forget about using a cursor for this task. It's not needed.
March 12, 2010 at 4:35 pm
Scott,
Take Lutz' advice here. The task is many times simpler to use the update statement he suggested and perform this query in a set-based fashion. Do not use a cursor here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2010 at 8:02 am
Hi Sorry for the late reply, thanks for the information much apreciated,
Im just curious how would you write a CURSOR script though? Would some one be able to write a basic one and maybe give an explanation of it the only reason i ask is because i would like to build upon my knowledge of what a CURSOR is.
Thanks in Adavnce guys.
March 13, 2010 at 9:55 am
scott.atkins (3/13/2010)
Hi Sorry for the late reply, thanks for the information much apreciated,Im just curious how would you write a CURSOR script though? Would some one be able to write a basic one and maybe give an explanation of it the only reason i ask is because i would like to build upon my knowledge of what a CURSOR is.
Thanks in Adavnce guys.
All I can do is point you at BOL (BooksOnline), the SQL Server help system installed together with SQL Server. Search for DECLARE C U R S O R (you need to remove the spaces in between but I couldn't make my fingers typing that word without it) 😉
Honestly: Try to improve your set based skills and forget about how to write a c....r. There are just a few scenarios where it would be needed. And if you think you cannot solve a request without it, get back here and there will be many of us willing to show you how to do it set based.
March 14, 2010 at 6:24 am
Quick update, This is far as what i have got to writing a Cursor to update one table from the information selected from a previous table. Im not going to be using it as you guys had mentioned a different way to Select and update a table but im just looking for a bit of feedback if possible. i have looked on the internet and this is as far as what i have got so far.
-- =============================================
-- Author:<Scott Atkinson>
-- Create date: <4/12/2010>
-- Description:<Transfering Information from one table to another>
-- =============================================
USE Record
--Declared the Cursor
DECLARE up_date CURSOR
FOR
--Selecting the Table/Column on which I want information pulled
--Back from
SELECT ContactStreet FROM Address ORDER BY AddressID
--Now I have opened the Cursor its ready to be Excuted.
OPEN up_date
--Asking the Cursor to FETCH the information from the Table/
--Column stated above.
FETCH NEXT FROM up_date
ALTER PROCEDURE [dbo].[FirstOne]
AS
--Asking Procedure to alter a specific Table
ALTER TABLE [TABLENAME] ADD [COLUMNNAME] NVARCHAR(60) AFTER name; [LASTCOLUMN]
--Selecting the information from a Table
SELECT [INFORMATION] FROM [TABLENAME]
--Inserting the information from the selected table before.
INSERT INTO [NEWCOLUMNNAME] WHERE
--Begin the Procedure.
BEGIN
SET NOCOUNT ON;
END
GO
Im aware i do have to close and delete the cursor after but i will add that additional information after just looking for some feedback.
Thanks in advance guys.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply