Need help on building an MS/sql Instert Tatement

westdh
westdh used Ask the Experts™
on
I need to create from a set of 14 rows in table Nop_CustomerAttribute two
MS/SQL Insert Statements that will Insert two row of data into the table Nop_Address.

The tables below are the Previous Results:

More Detail:

The Step are Nop_CustomerAttribute is Auto Created By the program. What I need is to be able
to read the 14 rows of the table Nop_CustomerAttribute and build an Sql Insert that will install
Two row of data into the table Nop_Address.

The first row value for the field IsBillingAddress would be 'false' and the second row value for
the field IsBillingAddress would be 'true'

The CustomerId Field in table Nop_Address would be the same for the two as its value in Nop_CustomerAttribute
The AddressId in table Nop_Address is the primary key and it would be auto incremented.

All the rest of the fields for table Nop_Address would be the same for the two rows.
--------------------------------------------------------------------
Table Name:  Nop_CustomerAttribute
 
CustomerAttributeId	int	Unchecked
CustomerId	int	Unchecked
[Key]	nvarchar(100)	Unchecked
Value	nvarchar(1000)	Unchecked
 
519	50	Gender	F
520	50	FirstName	Mary
521	50	LastName	Allen
522	50	DateOfBirth	<?xml version="1.0" encoding="utf-16"?> <dateTime>1980-07-01T00:00:00</dateTime>
523	50	Company	
524	50	StreetAddress	22222 Allen
525	50	StreetAddress2	
526	50	ZipPostalCode	97524
527	50	City	Eagle Point
528	50	PhoneNumber	888-888-8888
529	50	FaxNumber	
530	50	CountryID	1
531	50	StateProvinceID	46
532	50	Newsletter	False
 
-----------------------------------------------------------------
Table Name: Nop_Address
 
AddressId	int	Unchecked
CustomerID	int	Unchecked
IsBillingAddress	bit	Unchecked
FirstName	nvarchar(100)	Unchecked
LastName	nvarchar(100)	Unchecked
PhoneNumber	nvarchar(50)	Unchecked
Email	nvarchar(255)	Unchecked
FaxNumber	nvarchar(50)	Unchecked
Company	nvarchar(100)	Unchecked
Address1	nvarchar(100)	Unchecked
Address2	nvarchar(100)	Unchecked
City	nvarchar(100)	Unchecked
StateProvinceID	int	Unchecked
ZipPostalCode	nvarchar(10)	Unchecked
CountryID	int	Unchecked
CreatedOn	datetime	Unchecked
 
107	50	false	Mary	Allen	888-888-8866	mary@test8.com			22222 Allen		Eagle Point	46	97524	1	2009-07-21 06:17:09.526
108	50	True	Mary	Allen	888-888-8866	mary@test8.com			22222 Allen		Eagle Point	46	97524	1	2009-07-21 06:17:09.517

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I belive this is a Pivot function

Author

Commented:
sql server 2008 pivot rows to columns and then insert the row into another table
How to do this is what I need info on.. I am just finding out it is a pivot..I think
Yes, it is something like pivot and it can be done e.g. this way:
CREATE TABLE Nop_Address (
   AddressId int NOT NULL identity(1,1), 
   IsBillingAddress bit NOT NULL, 
   CustomerId int NOT NULL, 
   FirstName nvarchar(100) NULL,
   LastName	nvarchar(100) NULL, 
   PhoneNumber nvarchar(50) NULL, 
   Email nvarchar(255) NULL, 
   FaxNumber nvarchar(50) NULL, 
   Company	nvarchar(100) NULL, 
   Address1 nvarchar(100) NOT NULL, 
   Address2 nvarchar(100) NULL, 
   City nvarchar(100) NOT NULL,
   StateProvinceID int NULL, 
   ZipPostalCode nvarchar(10) NULL, 
   CountryID int NOT NULL, 
   CreatedOn datetime NOT NULL)
 
INSERT INTO Nop_Address 
   (IsBillingAddress, CustomerId, FirstName, LastName, 
    PhoneNumber, FaxNumber, Company, 
    Address1, Address2, City, StateProvinceID, 
    ZipPostalCode, CountryID, CreatedOn)
  SELECT 0, CustomerId, 
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName, 
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName, 
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber, 
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber, 
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company, 
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1, 
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2, 
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City, 
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID, 
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode, 
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID, 
         GETDATE() AS CreatedOn
    FROM Nop_CustomerAttribute
   GROUP BY CustomerId
 
INSERT INTO Nop_Address 
   (IsBillingAddress, CustomerId, FirstName, LastName, 
    PhoneNumber, FaxNumber, Company, 
    Address1, Address2, City, StateProvinceID, 
    ZipPostalCode, CountryID, CreatedOn)
  SELECT 1, CustomerId, 
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName, 
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName, 
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber, 
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber, 
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company, 
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1, 
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2, 
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City, 
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID, 
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode, 
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID, 
         GETDATE() AS CreatedOn
    FROM Nop_CustomerAttribute
   GROUP BY CustomerId

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I am wrong this not a pivot it is just a --> inserting row data from one table into a new table  of colums
To show the primary key creation is probably not necessary, but...

ALTER TABLE Nop_Address ADD CONSTRAINT pk_Nop_Address PRIMARY KEY (AddressID)

Foreign key creation is also recommended for CustomerId, CountryId, and StateProvinceID
The 0 value in StateProvinceID should be updated to null where necessary.
If you are creating one row from several rows by transforming column values to column names then it is pivot.

Author

Commented:
I need to Insert the Multiple rows from on Table into another Table as a single row
OK, does the provided code work for you as you supposed?

Author

Commented:
I am getting two errors on inserting the two rows.

Because There is a relations ship to another Table:
dbo.Nop_Customer this is because: I am testing the script (using the query Analiser) against previously created data. I need to make sure when I go live that the script uses the CustomerID on the (Last) value in the Nop_CustomerAttribute table. How do I select the last CustomerID of the Nop_CustomerAttribute table.

Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Nop_Address_Nop_Customer1". The conflict occurred in database "Nop_CommernceDB", table "dbo.Nop_Customer", column 'CustomerID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 25
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Nop_Address_Nop_Customer1". The conflict occurred in database "Nop_CommernceDB", table "dbo.Nop_Customer", column 'CustomerID'.
The statement has been terminated.
The CustomerID conflict isn't a problem of INSERT statements but problem of your data stored in Nop_CustomerAttribute. Your Nop_CustomerAttribute table contains CustomerIDs which are not in Nop_Customer table yet.  What is the meaning of CustomerID in Nop_CustomerAttribute table? Do you have foreign key constraint for CustomerID defined in Nop_CustomerAttribute table?

If you are transforming data for given customers you cannot change their CustomerIDs otherwise your addresses would become related to wrong customers.

You have to define all customers having records in Nop_CustomerAttribute table OR you may solve it by filtering CustomerIDs:

INSERT INTO Nop_Address 
   (IsBillingAddress, CustomerId, FirstName, LastName, 
    PhoneNumber, FaxNumber, Company, 
    Address1, Address2, City, StateProvinceID, 
    ZipPostalCode, CountryID, CreatedOn)
  SELECT 0, CustomerId, 
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName, 
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName, 
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber, 
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber, 
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company, 
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1, 
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2, 
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City, 
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID, 
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode, 
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID, 
         GETDATE() AS CreatedOn
    FROM Nop_CustomerAttribute
   WHERE CustomerId IN (SELECT CustomerId FROM Nop_Customer)
   GROUP BY CustomerId

Open in new window

If you need the last CustomerId from Nop_CustomerAttribute to test just one record insertion then you have to change the above WHERE part:

   WHERE CustomerId = (SELECT MAX(CustomerId) FROM Nop_CustomerAttribute)

Author

Commented:
This is what I have now and I am getting this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Eagle Point' to data type int.

INSERT INTO dbo.Nop_Address
   (CustomerId, IsBillingAddress, FirstName, LastName,
    PhoneNumber,  FaxNumber, Company, Address1, Address2, City,
    StateProvinceID, ZipPostalCode, CountryID, CreatedOn)
  SELECT 0, CustomerId,'false',
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName,
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName,
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber,
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber,  
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company,  
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1,
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2,
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City,
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID,
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode,
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID

    FROM Nop_CustomerAttribute
    WHERE CustomerId IN (SELECT CustomerId FROM Nop_Customer where CustomerID='52')
    GROUP BY CustomerId
 
INSERT INTO dbo.Nop_Address                                
   (CustomerID, IsBillingAddress, FirstName, LastName,
    PhoneNumber,  FaxNumber, Company, Address1, Address2, City,
    StateProvinceID, ZipPostalCode, CountryID, CreatedOn)
  SELECT 1, CustomerId,'true',
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName,
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName,
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber,
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber,  
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company,  
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1,
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2,
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City,
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID,
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode,
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID

    FROM Nop_CustomerAttribute
    WHERE CustomerId IN (SELECT CustomerId FROM Nop_Customer where CustomerID='52')
    GROUP BY CustomerId


You added 'false' and 'true' to the select list but you did not update the INSERT column list properly...

What should mean these 'false' and 'true'? If it is replacement of some bit values (IsBillingAddress column) then it is not correct. The bit column can contain just 0 or 1 and nothing else.

In my original answer was:
INSERT INTO Nop_Address
   (IsBillingAddress, CustomerId, FirstName, ...
  SELECT 1, CustomerId, ...

You changed it to
INSERT INTO dbo.Nop_Address
   (CustomerId, IsBillingAddress, FirstName, ...
  SELECT 1, CustomerId,'false', ...

Do you see the inconsistency in number of values and data types?

Also if you are inserting 1 or 0 to CustomerId then the foreign key constraint violation is possible.

Please compare the list of fileds in the INSERT against the list created by SELECT, fix incompatibilities in data types and it will work.

Author

Commented:
Here is the final solution... thanks for all your help. Now all I need to do is find the email location and insert it into the files and it willbe done.

 
INSERT INTO dbo.Nop_Address7
   (CustomerID,IsBillingAddress, FirstName, LastName,
    PhoneNumber, Email, FaxNumber, Company, Address1, Address2, City,
    StateProvinceID, ZipPostalCode, CountryID, CreatedOn)
   
  SELECT CustomerId,0,
 
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName,
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName,
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber,
         ' ',
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber,  
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company,  
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1,
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2,
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City,
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID,
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode,
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID,
         GETDATE() AS CreatedOn


    FROM Nop_CustomerAttribute
    WHERE CustomerId = (SELECT MAX(CustomerId) FROM Nop_CustomerAttribute)
    GROUP BY CustomerId
 
INSERT INTO dbo.Nop_Address7                                
   ( CustomerID,IsBillingAddress, FirstName, LastName,
    PhoneNumber, Email, FaxNumber, Company, Address1, Address2, City,
    StateProvinceID, ZipPostalCode, CountryID, CreatedOn)
   
  SELECT  CustomerId,1,
         MAX(CASE WHEN [Key]='FirstName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) FirstName,
         MAX(CASE WHEN [Key]='LastName' THEN CAST(Value AS nVarchar(100)) ELSE '' END) LastName,
         MAX(CASE WHEN [Key]='PhoneNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) PhoneNumber,
          '',
         MAX(CASE WHEN [Key]='FaxNumber' THEN CAST(Value AS nVarchar(50)) ELSE '' END) FaxNumber,  
         MAX(CASE WHEN [Key]='Company' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Company,  
         MAX(CASE WHEN [Key]='StreetAddress' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address1,
         MAX(CASE WHEN [Key]='StreetAddress2' THEN CAST(Value AS nVarchar(100)) ELSE '' END) Address2,
         MAX(CASE WHEN [Key]='City' THEN CAST(Value AS nVarchar(100)) ELSE '' END) City,
         MAX(CASE WHEN [Key]='StateProvinceID' THEN CAST(Value AS int) ELSE 0 END) StateProvinceID,
         MAX(CASE WHEN [Key]='ZipPostalCode' THEN CAST(Value AS nVarchar(10)) ELSE '' END) ZipPostalCode,
         MAX(CASE WHEN [Key]='CountryID' THEN CAST(Value AS int) ELSE 0 END) CountryID,
         GETDATE() AS CreatedOn

    FROM Nop_CustomerAttribute
    WHERE CustomerId = (SELECT MAX(CustomerId) FROM Nop_CustomerAttribute)
    GROUP BY CustomerId

Author

Commented:
Very good Job ...set me on the right track. Thank You Very much
You are welcome!

Author

Commented:
I have run the solution in query analyzer and it runs correctly It does not create duplicate records. And the original problem is corected.
But in my application which is an ASP.NET VS2008 C# 3.5. It does create duplicate records...

I believe this is due to somehow the script is being executed twice...I have put this problem up for someone help. Thanks You
What do you mean by duplicate records? Instead of two records for each customer you have 4 records inserted?

You could extend the WHERE to avoid inserts causing duplicities:
instead of
WHERE CustomerId = (SELECT MAX(CustomerId) FROM Nop_CustomerAttribute)
 
use
WHERE CustomerId = (SELECT MAX(CustomerId) FROM Nop_CustomerAttribute)
   AND CustomerId NOT IN (SELECT CustomerId FROM dbo.Nop_Address WHERE IsBillingAddress = !!place 1 or 0 here depending on the insert!!  )

Better way is to remove the reason of duplicate script execution, of course.

And yes, the best way is to ask in ASP.NET zone with appropriate code attached.

Author

Commented:
Thar did it Thanks again...I have the proble also listed here on expert exchange how can I award you the points. Or find the question and I'll closit in your favor.

2  07/22/09 08:12 AM 500 Having Problem Creating two... 2 WebApplicati...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial