Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create tables in code in SQL server 2008?!?!


Create tables in code in SQL server 2008?!?!

Author
Message
thekidheat
thekidheat
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Hello everyone,

I have a project from school I am working and I have to create tables in code in SQL server 2008 but havn a prob. I am learning everything online and it is diffucult. Can't figure out how to convert the money CHAR so it recognizes it. When I ex...ecute it, it says I have a syntax error. Any advice would be great. Thank you. Im getting error: 235 state 0 line 1 Level 16: Cannot convert a char value to money. The char value has incorrect syntax. Here is my code:

Create Database [KUDLER]

Create Table [EMPLOYEETABLE]
(
[EmployeeID] [int] IDENTITY (1,1) NOT NULL,
[LastName] [VARCHAR] (25) NOT NULL,
[FirstName] [VARCHAR] (25) NOT NULL,
[Address] [VARCHAR] (max) NULL,
[City] [VARCHAR] (max) NOT NULL,
[State] [CHAR] (2) NOT NULL,
[AreaCode] [CHAR] (3) NOT NULL,
[TelephoneNumber] [CHAR] (7) NOT NULL,
[EmployerInfoReport] [VARCHAR] (max) NOT NULL,
[HireDate] [DATE] NOT NULL,
[Salary] [MONEY] NOT NULL,
[Gender] [CHAR] (1) NOT NULL,
[Age] [CHAR] (2) NOT NULL,
[JobTitle] [VARCHAR] (max) NOT NULL
)
SET IDENTITY_INSERT [EMPLOYEETABLE] ON
Go
INSERT INTO [EMPLOYEETABLE] ( [EmployeeID], [FirstName], [LastName], [Address], [City], [State], [AreaCode], [TelephoneNumber], [EmployerInfoReport], [HireDate], [Salary], [Gender], [Age], [JobTitle])
VALUES('0001', 'Glenn', 'Edelman', '175 Bishop Lane', 'La Jolla', 'Ca', '619', '5550199', 'None', '10/07/03', '11.25/hr', 'M', '25', 'Cashier'),
('0002', 'Eric', 'McMullen', '763 Church st.', 'Lemon Grove', 'Ca', '619', '5550135', 'None', '12/01/02', '7.00/hr', 'M', '23', 'Bagger'),
('0003', 'Raj', 'Slentz', '123 Torrey dr.', 'North Clairmont', 'Ca', '619', '5550123', 'None', '05/15/00', '50,000.00/yr', 'M', '30', 'Asst.Store Manager'),
('0004', 'Erin', 'Broun', '2045 Parkway apt 2B', 'Encinitas', 'Ca', '760', '5550100', 'None', '03/01/03', '7.25/hr', 'F', '25', 'Bagger'),
('0005', 'Donald', 'Carpenter', '927 Second st.', 'Encinitas', 'Ca', '619', '5550154', 'None', '10/15/2003', '7.75/hr', 'M', '25', 'Stocker'),
('0006', 'David', 'Esquivez', '10983 N.Coast Hwy apt 902', 'Encinitas', 'Ca', '760', '5550108', 'None', '9.75/hr', '07/01/03', 'M', '27', 'Asst. Butcher/Seafood'),
('0007', 'Nancy', 'Sharp', '10793 Monticino rd', 'Romona', 'Ca', '858', '5550135', 'None', '06/01/03', '10.50/hr', 'F', '35', 'Cashier')
Go
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 Visits: 4639
1- You have to code one insert statement for each row you want to add to the target table.

2- You cannot insert '11.25/hr' into a currency data type - currency is basically a number datatype.

I wouldn't define any column as varchar(max), in this particular case max size for each one of them is easily inferable.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2286 Visits: 7830
PaulB-TheOneAndOnly (5/22/2010)
1- You have to code one insert statement for each row you want to add to the target table.

2- You cannot insert '11.25/hr' into a currency data type - currency is basically a number datatype.

I wouldn't define any column as varchar(max), in this particular case max size for each one of them is easily inferable.


Actually, if this is SQL 2008 (and this is a SQL2008 forum) then the syntax is correct, you do not need multiple insert statements.

You are correct that the problem is trying to insert values like '11.25/hr' (which is a char type value) into a MONEY column...

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • angusjamesgilmour
    angusjamesgilmour
    Grasshopper
    Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

    Group: General Forum Members
    Points: 18 Visits: 309
    Hi,

    As already identified by the two previous posters

    1) You need to have an insert statement preceeding each row you wish to insert into the table.
    2) Having varchar columns specified as max is unneccesary as you should be able to specify an acceptable length.
    3) SQL Server is unable to convert the char data to the money which is a numeric type. e.g. the three characters of each example have either "/yr" or "/hr" which SQL Server is interpreting as being a string whihc it can't implicitly convert.

    In addition I would suggest the following.

    A) The salary column seems to be used to hold different types of information. e.g for most entries it appears to be holding an hourly rate, but for one example it holds a yearly salary. I would suggest introducing a new column called SalaryType and use this to record if the Salary is an hourly rate or a yearly salary. This would allow you to retain the rate type information.

    B) I would also suggest changing the age column to hold the actual date of birth of the individual, the actual age could then simply be calculated. If you only hold the actual age it would be incorrect once the persons next birthday passes.

    Hope this helps.

    Angus
    thekidheat
    thekidheat
    Forum Newbie
    Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

    Group: General Forum Members
    Points: 2 Visits: 2
    Thank all of you for your help!! Smile No more errors! )
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search