CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4324

Create table statement for multiple columns

If I perform a create table like below in T-SQL based on that select statement the previously existed:

create table #CodesToVoid
(
        PromotionId int, 
        PromotionId int, 
        Code nchar(10), 
        BookingReference nvarchar(50)   
)

INSERT #CodesToVoid
   SELECT 
       p.PromotionId [MasterPromotionID], 
       pc.PromotionId, pc.Code, pc.BookingReference  
   FROM
       J2H.dbo.Promotions p...

I find it strange to have to promotionID as one is coming from alias p. and the other from alias pc. If in the create table statement I call one of the PromotionId as:

PromotionId [MasterPromotionID] int

It displays a syntax error. Same if I do it as:

PromotionId AS MasterPromotionID int

My question is that I need two promotionId in the create table in this situation because the select statement has two of these?

Upvotes: 1

Views: 173

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

If you do it with "Create Table" first, then the fieldnames in that table has no relation to the fieldNames in your select. They are matched up positionally. ie:

create table #CodesToVoid
        (
        MasterPromoId int, 
        PromoId int, 
        Code nchar(10), 
        BookingReference nvarchar(50)   
        );

INSERT #CodesToVoid
SELECT p.PromotionId, pc.PromotionId, pc.Code, pc.BookingReference  
from J2H.dbo.Promotions p...

However, for something like this, you wouldn't first create a table. You would just "Select into" a table. It does the "create table" part for you. ie:

SELECT p.PromotionId as [MasterPromotionID], 
   pc.PromotionId, 
   pc.Code, 
   pc.BookingReference  
INTO #CodesToVoid
from J2H.dbo.Promotions p...

If you would use Create Table approach, then for locally used tables "a table variable" might be a better choice than a temp table. ie:

declare @CodesToVoid table
        (
        MasterPromoId int, 
        PromoId int, 
        Code nchar(10), 
        BookingReference nvarchar(50)   
        );

INSERT @CodesToVoid
SELECT p.PromotionId, pc.PromotionId, pc.Code, pc.BookingReference  
from J2H.dbo.Promotions p...

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You cannot have the same idenfitifier for 2 columns. Create your table as:

CREATE TABLE #CodesToVoid(
        MasterPromotionID INT, 
        PromotionId INT, 
        Code NCHAR(10), 
        BookingReference NVARCHAR(50));

INSERT INTO #CodesToVoid(MasterPromotionID,PromotionId,Code,BookingReference)
SELECT p.PromotionId,      -- aliasing change nothing here 
   pc.PromotionId,         -- data will be inserted to column specified in column list
   pc.Code,
   pc.BookingReference  
FROM J2H.dbo.Promotions p...

Alternatively use SELECT * INTO:

SELECT p.PromotionId [MasterPromotionID],
       pc.PromotionId,
       pc.Code,
       pc.BookingReference  
INTO #CodesToVoid
FROM J2H.dbo.Promotions p...

Keep in mind that creating SELECT * INTO ... more than once may return error that table exists for current connection.

Upvotes: 4

Related Questions