How to preserve leading zeros with insert into

  • nevada19785

    Valued Member

    Points: 65






    Below is the create table script and the insert into statement. Both work fine in SSMS. However, the insert into drops my leading zeros. These are important because I have a query that I want to test out that clearly specifics certain character counts and values. I am hoping to not have to re-write a very lengthy and complex query. Is there a way to do this using the insert into statement and not having to import using the import wizard.?


    Insert into cestest (stfips,areatype,area,periodyear,periodtype,period,seriescode,adjusted,benchmark,prelim,empces,empprodwrk,empfemale,hours,earnings,hourearn,supprecord,supphe,supppw,suppfem,hoursallwrkr,earningsallwrkr,hourearnallwrkr,suppheallwrkr)

    Values (32,01,000000,2019,03,11,00000000,0,2018,0,1453600,0,0,0.0,0.00,0.00,0,1,1,1,0.0,0.00,0.00,1),




    CREATE TABLE cestest


    stfips char(2) NOT NULL,

    areatype char(2) NOT NULL,

    area char(6) NOT NULL,

    periodyear char(4) NOT NULL,

    periodtype char(2) NOT NULL,

    period char(2) NOT NULL,

    seriescode char(8) NOT NULL,

    adjusted char(1) NOT NULL,

    benchmark char(4) NULL,

    prelim char(1) NULL,

    empces numeric(9) NULL,

    empprodwrk numeric(9) NULL,

    empfemale numeric(9) NULL,

    hours numeric(3,1) NULL,

    earnings numeric(8,2) NULL,

    hourearn numeric(6,2) NULL,

    supprecord char(1) NULL,

    supphe char(1) NULL,

    supppw char(1) NULL,

    suppfem char(1) NULL,

    hoursallwrkr numeric(3,1) NULL,

    earningsallwrkr numeric(8,2) NULL,

    hourearnallwrkr numeric(6,2) NULL,

    suppheallwrkr char(1) NULL


  • nevada19785

    Valued Member

    Points: 65

    Not sure why it did not work the first time but put single quotes around each item in the values and it works. Previously, the values all turned red in SSMS and got an incorrect syntax error.

  • BTylerWhite

    Mr or Mrs. 500

    Points: 550

    Nevada, as you mentioned the single quotes around each item being inserted into the corresponding "char" columns will fix the issue. The values turning red seems to indicate there was an additional or missing quote left somewhere. Just be sure that the single quotes are added to each row within the list of VALUES.

    Data Type Precedence will cause implicit conversion as numeric or decimal (or integer in this scenario) data types have higher precedence than the char data type. For example, if you were to do the following:

    DECLARE @Test table
    CharCol char (2) NOT NULL,
    NumericCol numeric (13, 2) NOT NULL

    INSERT INTO @Test (CharCol, NumericCol)
    VALUES ('00', 5.43),
    (00, 5.43)

    SELECT *
    FROM @Test AS T;

    You'll notice although the first row in the list of values contained single quotes, it still inserted and removed the leading zeroes from both rows.

    • This reply was modified 2 days, 7 hours ago by  BTylerWhite.
    • This reply was modified 2 days, 7 hours ago by  BTylerWhite.
  • Phil Parkin

    SSC Guru

    Points: 243988

    Not directly related to the question, but surely SMALLINT would have been a better datatype for PeriodYear?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • nevada19785

    Valued Member

    Points: 65

    @Phil Parkin,

    You are probably correct. However, this schema/structure is not decided upon by me.

  • nevada19785

    Valued Member

    Points: 65

    Maybe I should create a new question but using that create table script and insert into statement, trying to run this query and getting no results. Any thoughts as to why?

    WITH c1 AS

    (SELECT e.stfips, e.areatype, e.area, e. periodyear, e.period, e.seriescode, e.empces

    FROM cestest as e

    WHERE e.periodtype='03'

    And e.supprecord='0'

    and e.periodyear=

    (Select Max(periodyear)

    From cestest)

    and e.period=

    (Select Top(1)period

    From cestest

    Order by periodyear desc, period desc)

    and e.stfips='32'

    and e.adjusted='1'

    and e.areatype='01'


    C2 AS

    (Select Distinct c1.periodyear, c1.period

    From c1


    C3 As


    (Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As 'month',

    (Case When c2.period='01' Then (c2.periodyear -1) Else (c2.periodyear) END) As 'year'

    From C2


    C4 AS

    (Select c.stfips, c.areatype, c.area, c.periodyear, c.period, c.seriescode, c.empces

    From cestest as c, c2

    Where c.period = c2.period

    And c.periodyear = (c2.periodyear-1)

    And c.supprecord='0'

    and c.stfips='32'

    and c.adjusted='1'

    and c.areatype='01'


    C5 AS

    (Select s.stfips, s.areatype, s.area, s.periodyear, s.period, s.seriescode, s.empces

    From cestest as s, C1, C3

    Where s.supprecord='0'

    And s.period = c3.month

    And s.periodyear = c3.year

    and s.stfips='32'

    and s.adjusted='1'

    and s.areatype='01'


    C6 As

    (Select d.seriescode, d.seriesttls, d.stfips

    From cescode d, C1

    Where d.stfips=C1.stfips

    And d.seriescode=C1.seriescode

    And (d.serieslvl ='0' Or d.serieslvl='2' Or d.serieslvl='7')


    Select Distinct C6.seriesttls as 'Super Sector', c6.seriescode As 'Code', C1.empces As 'Employment(p)', C5.empces As 'Last Month', C4.empces As 'Last Year',

    c1.empces-c5.empces As 'Net Change Mnth',

    (Cast(Cast(((c1.empces /(c5.empces)-1)*100 )AS decimal (6,2) )as varchar)+ '%') '% Chg Mnth',

    c1.empces-c4.empces As 'Net Change Year',

    (Cast(Cast(((c1.empces /(c4.empces)-1)*100 )AS decimal (6,2) )as varchar)+ '%') '% Chg Year'

    From C1, C4, C5, C6

    Where (c6.seriescode = c1.seriescode and c6.seriescode = c4.seriescode and c6.seriescode = c5.seriescode)

    order by c6.seriescode



  • jcelko212 32090

    SSCrazy Eights

    Points: 8920

    >> Below is the CREATE TABLE script [sic: statement] NSERT INTO drops my leading zeros. These are important because I have a query that I want to test out that clearly specifics certain character counts and values. <<

    Your problem is that you don't understand how SQL and RDBMS work. The concept of leading zeros makes no sense whatsoever. That's a display function and the data in SQL is in some unspecified internal format (no, we didn't even say they were binary when we were writing the standards). The idea is that there is a display layer or tier in RDBMS that receives the unspecified abstract data and formats it. Now looking at you did post, which was almost usable, you're better than 70 to 80% of the other noobs on these forums.

    You don't understand that by definition, not as an option, a table must have a key.

    You have no concept whatsoever of ISO 11179 naming rules or a valid data model. You don't seem to understand the difference between an attribute and attribute property and a value. For example, there is no such thing as a generic "period"; when you had your logic courses in college. did you remember the law of identity? (To be is to be something in particular; to be nothing in particular or anything in general, is to be nothing at all). So this has to be by the most fundamental law of Western logic "<something, in particular,>_period" and then it can have attribute properties. In the ISO model data elements are defined as "<attribute>_<attribute property>" when you declare your tables.

    Virtually every one of your columns has some fundamental conceptual error. When you mix things like "area" preceded by "area_type" this implies you have a denormalized schema that mixes multiple tables together. For example, why is there not a reference to the "Something_Periods" table, whose columns include "period_year CHAR(4)) NOT NULL CHECK (period_year LIKE '[12][0-9][0-9][0-9]') " and "period_type CHAR(2) NOT NULL CHECK(period_type IN (...))"

    I also hope in spite of what I've seen in your posting, that you are not using bit flags in SQL. We did that in assembly language programming and gave it up decades ago because we knew how to design a schema.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply