Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

another sql insert

Posted on 2010-01-07
2
Medium Priority
?
211 Views
Last Modified: 2012-05-08
want to create spreadsheet9
very similiar to spreadsheet8 which works


spreadsheet9 includes
countrycode table
which has values countrycode, shipcountry, countrynumber

is the insert for spreadsheet9 correct
create table spreadsheet8(
[dateordered] [datetime] NULL,
[blank1] [varchar](50) NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[blank2] [varchar](50) NULL,
[deliveryservicecode][int] NULL,
[blank3] [varchar](50) NULL,
[companyname] [varchar](100) NULL,
[shipaddress1] [varchar](100) NULL,
[shipaddress2] [varchar](100) NULL,
[shipaddress3] [varchar](100) NULL,
[blank4] [varchar](50) NULL,
[shipzip] [varchar](15) NULL,
[blank5] [varchar](50) NULL,
[country] [varchar] (90) NULL,
[contactname] [varchar](100) NULL,
[shipphone1] [varchar](50) NULL
)

INSERT INTO spreadsheet8 ([dateordered], [orderid], [internalsku], [quantity], [deliveryservicecode], 
      [companyname], [shipaddress1], [shipaddress2],[shipaddress3], [shipzip], [country],[contactname], [shipphone1]) 
SELECT o.dateordered,o.OrderID,p.internalsku,i.Quantity,12, RTRIM ( LTRIM( o.shipfirstname + ' ' + o.shiplastname ) ) ,o.shipaddress1,o.shipaddress2,o.shipcity, o.shipzip,o.shipcountry, RTRIM ( LTRIM( o.shipfirstname + ' ' + o.shiplastname ) ) ,shipphone1 
from  
        products p 
        inner join orderitems i on  p.productid = i.productid 
        inner join orders o on o.orderid = i.orderid 
where i.orderid = @x





create table spreadsheet9(
[blank1] [varchar](50) NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[blank2] [varchar](50) NULL,
[countrynumber][int] NULL,
[blank3] [varchar](50) NULL,
[companyname] [varchar](100) NULL,
[shipaddress1] [varchar](100) NULL,
[shipaddress2] [varchar](100) NULL,
[shipaddress3] [varchar](100) NULL,
[blank4] [varchar](50) NULL,
[shipzip] [varchar](15) NULL,
[blank5] [varchar](50) NULL,
[countrycode] [varchar] (90) NULL,
[contactname] [varchar](100) NULL,
[shipphone1] [varchar](50) NULL
)
INSERT INTO spreadsheet9 ([orderid], [internalsku], [quantity], [deliveryservicecode], 
      [companyname], [shipaddress1], [shipaddress2],[shipaddress3], [shipzip], [country],[contactname], [shipphone1]) 
SELECT o.OrderID,p.internalsku,i.Quantity,c.countrynumber, RTRIM ( LTRIM( o.shipfirstname + ' ' + o.shiplastname ) ) ,o.shipaddress1,o.shipaddress2,o.shipcity, o.shipzip,c.countrycode, RTRIM ( LTRIM( o.shipfirstname + ' ' + o.shiplastname ) ) ,shipphone1 
from  
        products p 
        inner join orderitems i on  p.productid = i.productid 
        inner join orders o on o.orderid = i.orderid
		inner join countrycode c on c.shipcountry = o.shipcountry 
where i.orderid = @x

Open in new window

0
Comment
Question by:rgb192
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 26204843
assuming that [shipaddress3] = ShipCity , that seems perfect
0
 
LVL 4

Assisted Solution

by:igni7e
igni7e earned 1000 total points
ID: 26204966
yes it looks good, why do u ask? why not run it?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question