CODEWITHSUNDEEP
sqlsql-serverms-access
user3496218
user3496218

Reputation: 207

INSERT INTO - Access SQL vs SQL Server

I have a random question that came up when I was trying to take the Access SQL code for an INSERT INTO statement and translate to a more organized SQL Server version. Below is the Access SQL and I can tell what everything is pointing towards except for the first SELECT - "SELECT "Shipment" AS Type...I don't see any source it is pulling data from and it is called an expression in the design view of Access. I have really only worked in SQL Server so I am hoping somebody can help me decipher what the intention was here. Thanks.

'INSERT INTO [Fcst/Order/Ship Data] ( Type, [Acct#], BU, Gender, Material, Description, [Size], [Year], Mth, Qty, BS )
SELECT "Shipment" AS Type, [Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)].[BILL TO] AS [Acct#], dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC AS BU, dbo_vw_kd_GENDER.GENDER_DESC AS Gender, dbo_VW_INVOICES_SIZE.MATERIAL AS Material, dbo_vw_MM_MATERIAL.DESCRIPTION AS Description, dbo_VW_INVOICES_SIZE.SIZE_LITERAL, Year([requested_delivery_date]) AS [Year], Month([requested_delivery_date]) AS Mth, Sum(dbo_VW_INVOICES_SIZE.INVOICED_QUANTITY) AS Qty, dbo_vw_kd_BUSINESS_SEGMENT.BUSINESS_SEGMENT_DESC
FROM ([Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)] INNER JOIN (dbo_VW_INVOICES_SIZE INNER JOIN ((dbo_vw_MM_MATERIAL INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE = dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE) INNER JOIN dbo_vw_kd_GENDER ON dbo_vw_MM_MATERIAL.GENDER_CODE = dbo_vw_kd_GENDER.GENDER_CODE) ON dbo_VW_INVOICES_SIZE.MATERIAL = dbo_vw_MM_MATERIAL.MATERIAL) ON ([Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)].SIZE_INDEX = dbo_VW_INVOICES_SIZE.SIZE_LITERAL) AND ([Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)].BILLING_DOCUMENT_NUMBER = dbo_VW_INVOICES_SIZE.BILLING_DOCUMENT_NUMBER) AND ([Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)].BILLING_ITEM_NUMBER = dbo_VW_INVOICES_SIZE.BILLING_ITEM_NUMBER)) INNER JOIN dbo_vw_kd_BUSINESS_SEGMENT ON dbo_vw_MM_MATERIAL.BUSINESS_SEGMENT_CODE = dbo_vw_kd_BUSINESS_SEGMENT.BUSINESS_SEGMENT_CODE
GROUP BY [Fcst v Order 03: Create Temp Invoice Tbl (Invoiced)].[BILL TO], dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC, dbo_vw_kd_GENDER.GENDER_DESC, dbo_VW_INVOICES_SIZE.MATERIAL, dbo_vw_MM_MATERIAL.DESCRIPTION, dbo_VW_INVOICES_SIZE.SIZE_LITERAL, Year([requested_delivery_date]), Month([requested_delivery_date]), dbo_vw_kd_BUSINESS_SEGMENT.BUSINESS_SEGMENT_DESC
HAVING (((Year([requested_delivery_date]))=2017));

Upvotes: 0

Views: 68

Answers (2)

Andre
Andre

Reputation: 27644

It's just a constant string that goes into [Fcst/Order/Ship Data].Type for all inserted records.

You would do the same in SQL Server, just with single quotes instead of double quotes.

Edit:

In SQL Server you would do

INSERT INTO foo (type, bar)
SELECT 'Hello World' AS type, bazbar
FROM baz

or perhaps use [type], since that's a reserved word in Transact-SQL (CREATE TYPE).

AS type isn't really needed, this would work just as well:

INSERT INTO foo (type, bar)
SELECT 'Hello World', bazbar
FROM baz

The column alias is useful for

  • testing the SELECT part separately
  • self-documenting the SQL code (for non-trivial statements)

Upvotes: 3

Phil M
Phil M

Reputation: 1619

If you're talking specifically about just the SELECT "Shipment" as Type, and not what follows, then what that is doing is just putting the string literal "Shipment" in the Type column of every row it adds.

See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql

Upvotes: 1

Related Questions