• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2139
  • Last Modified:

Programming in SQL (complex query)

My knowledge of SQL is limited to fairly basic queries and stored procedures.

However I need to perform a more complex query (that verges on scripting) whereby I use a select query to iterate through all the records in a customer table and then copy some of the fields to one table and get the ident of that new record, copy some other fields to another table and get that ident before inserting the values of the two idents to another table to link the tables up.

Here is the bare bones of the query (script):

SELECT [CustomerID]
      ,[EntryDate]
      ,[LastUpdateDate]
      ,[Title]
      ,[FirstName]
      ,[LastName]
      ,[DateofBirth]
      ,[Address1]
      ,[Address2]
      ,[Address3]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[LoweredPostalCode]
      ,[Country]
      ,[EmailAddress]
      ,[Telephone]
      ,[MailMarketing]
      ,[MaritalStatus]
      ,[Occupation]
      ,[OldCustomerID]
  FROM [Company].[dbo].[Customers]


/****** Insert some of the data from SELECT query into the Customer table ******/

INSERT INTO [Customer].[dbo].[Customer]
           ([EntryDate]
           ,[LastUpdateDate]
           ,[Title]
           ,[FirstName]
           ,[LastName]
           ,[EmailAddress]
           ,[Telephone]
           ,[MailMarketing]
           ,[DateofBirth]
           ,[MaritalStatus]
           ,[Occupation]
           ,[OldCustomerID])
     VALUES
           (<EntryDate, datetime,>
           ,<LastUpdateDate, smalldatetime,>
           ,<Title, nvarchar(20),>
           ,<FirstName, nvarchar(50),>
           ,<LastName, nvarchar(50),>
           ,<EmailAddress, nvarchar(50),>
           ,<Telephone, varchar(30),>
           ,<MailMarketing, bit,>
           ,<DateofBirth, smalldatetime,>
           ,<MaritalStatus, nchar(10),>
           ,<Occupation, nvarchar(50),>
           ,<OldCustomerID, int,>)

SET @CustomerID = @@IDENTITY

SELECT @CustomerID


/****** Insert some of the data from SELECT query into the Customer table ******/

INSERT INTO [Customer].[dbo].[Address]
           ([EntryDate]
           ,[Address1]
           ,[Address2]
           ,[Address3]
           ,[City]
           ,[Region]
           ,[PostalCode]
           ,[LoweredPostalCode])
     VALUES
           (<EntryDate, datetime,>
           ,<Address1, nvarchar(100),>
           ,<Address2, nvarchar(100),>
           ,<Address3, nvarchar(100),>
           ,<City, nvarchar(50),>
           ,<Region, nvarchar(50),>
           ,<PostalCode, nvarchar(20),
           ,<LoweredPostalCode, nvarchar(20),>)

SET @AddressID = @@IDENTITY

SELECT @AddressID


/****** Link the tables ******/

INSERT INTO [Customer].[dbo].[CustomerAddress]
           ([CustomerID]
           ,[AddressID]
           ,[AddressFromDate])
     VALUES
           (@CustomerID
           ,@AddressID
           ,<AddressFromDate, smalldatetime,>)

0
g-spot
Asked:
g-spot
1 Solution
 
assystCommented:

Are you in a stage where you can make the design changes. If so then I would suggest storing the CustomerID and AddressFromDate in [Customer].[dbo].[Address] table and removing [Customer].[dbo].[CustomerAddress] table.
 Then all you need to do is insert into [Customer].[dbo].[Customer] all the records from [Company].[dbo].[Customers] table and then insert into [Customer].[dbo].[Address] table using [Company].[dbo].[Customers]  by joining based on OldCustomerID and get the new CustomerID by joining [Customer].[dbo].[Customer].OldCustomerID  table.

 


 
0
 
g-spotAuthor Commented:
Thank you. Sorry for the delay in confirming.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now