Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create tables in code in SQL server 2008?!?! Expand / Collapse
Author
Message
Posted Saturday, May 22, 2010 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 23, 2010 5:45 PM
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
Post #926465
Posted Saturday, May 22, 2010 6:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #926468
Posted Sunday, May 23, 2010 12:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #926542
    Posted Sunday, May 23, 2010 1:16 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Friday, September 26, 2014 7:06 AM
    Points: 18, Visits: 192
    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
    Post #926548
    Posted Sunday, May 23, 2010 5:45 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, May 23, 2010 5:45 PM
    Points: 2, Visits: 2
    Thank all of you for your help!! :) No more errors! )
    Post #926576
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse