Create table statement for multiple columns

Question

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?

what is pc aliasing for which table?

Mukund
– Mukund

2015-12-18 10:39:43 +00:00
Commented Dec 18, 2015 at 10:39 — Mukund
– Mukund, Commented Dec 18, 2015 at 10:39
@Mukund tables: pc - promotioncode, p - promotions

BruceyBandit
– BruceyBandit

2015-12-18 10:40:43 +00:00
Commented Dec 18, 2015 at 10:40 — BruceyBandit
– BruceyBandit, Commented Dec 18, 2015 at 10:40

Lukasz Szozda · Accepted Answer · 2015-12-18 10:44:54Z

4

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.

edited Dec 18, 2015 at 10:44

answered Dec 18, 2015 at 10:38

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BruceyBandit Over a year ago

My apologies, we are trying to deviate from Select Into clauses (but useful for other users who may see this answer). If I call it 'MasterPromotionID in the create statement' - then does the select statement changes to just 'p.MasterPromotionID' or should it stay as it is?

Lukasz Szozda Over a year ago

@BruceyBandit After creation a table column name is constant. When you use INSERT SELECT the name of alias in select column list does not matter. Check my update and always specify column list when doing INSERT SELECT for clarity.

marc_s Over a year ago

@lad2025: your research dog is upside down :-)

Cetin Basoz · Accepted Answer · 2015-12-18 10:49:08Z

1

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...

answered Dec 18, 2015 at 10:49

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

3 Comments

BruceyBandit Over a year ago

Thank you for this answer Cetin, upvote. Reason not using Select Into is because of reference warnings in error list. So we using the Create Table first, and reason not declaring tables is a lot of testing will have to be done as affects performance and that will be a bugga

Cetin Basoz Over a year ago

I don't understand your comments really :) Maybe there is something you are interpreting wrong for 'select into', if you shared a sample to show what you meant, there could be explanations\corrections. One of the reasons I suggested table variable was performance and easier debugging :)

BruceyBandit Over a year ago

I did start off using variables but when I showed senior colleague, he says a lot of testing needs to be done because we have to rewrite a lot of script. This will take time and they need to get this deployed very soon so he said keep it to temp tables (hash tag) than variable temp tables for now

Collectives™ on Stack Overflow

Create table statement for multiple columns

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related