June 11, 2012 at 12:41 pm
HERE I AM HAVING A TABLE WHICH CONTAIN DECIMAL DATA TYPE SO PLZ TELL HOW TO INSERT DATA IN THIS TABLE
CREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal]((18, 4)) NOT NULL,
[DimensionC] [decimal](4, 4)) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL,
)
INSERT INTO [dbo].[tblPlotInfo]
(
[ProjectId]
,[DimensionA]
,[DimensionB]
,[DimensionC]
,[DimensionD]
,[SquareFeet]
)
VALUES
(
1
,1.23
,1.23
,146.23
,156.23
,13.23
)
June 11, 2012 at 12:48 pm
Same way you insert data into any other table, with an insert statement like the one you posted. What exactly is the insert doing or not doing?
Note that both your table definition and your select have syntax errors. The table definition has stray brackets, the insert references a column that doesn't exist. I assume that's just a cut and paste error
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 12:53 pm
Cleaned up your code and fixed some data types based on the sample data you were trying to insert.
CREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal](18, 4) NOT NULL,
[DimensionC] [decimal](10, 4) NOT NULL,
[DimensionD] [decimal](10, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL,
);
INSERT INTO [dbo].[tblPlotInfo]
(
[DimensionA]
,[DimensionB]
,[DimensionC]
,[DimensionD]
,[ActualPrice]
)
VALUES
(
1.23
,1.23
,146.23
,156.23
,13.23
);
GO
DROP TABLE [dbo].[tblPlotInfo];
GO
You may want to delete or comment out the DROP TABLE statement. I put it there to cleanup my sandbox database.
June 11, 2012 at 1:08 pm
THIS IS THE TABLE
CREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal]((18, 4)) NOT NULL,
[DimensionC] [decimal](4, 4)) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL
)
INSERT INTO [dbo].[tblPlotInfo]
(
[DimensionA], [DimensionB], [DimensionC], [DimensionD], [SquareFeet]
)
VALUES
(
1.23 ,1.23,146.23 ,156.23 ,13.23
)
BUT I GOT ERROR WHILE CREATING THE TABLE
Incorrect syntax near '('.
June 11, 2012 at 1:27 pm
sivag (6/11/2012)
BUT I GOT ERROR WHILE CREATING THE TABLEIncorrect syntax near '('.
Yes, because as we both said, you have stray brackets in the table definition. There's one line that has the brackets duplicated (and the error message would have told you which line the error is on.
You also are referencing a column in the insert that doesn't exist in the table. Just take a look at the query, you'll see the error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 1:27 pm
sivag (6/11/2012)
THIS IS THE TABLECREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal]((18, 4)) NOT NULL,
[DimensionC] [decimal](4, 4)) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL
)
INSERT INTO [dbo].[tblPlotInfo]
(
[DimensionA], [DimensionB], [DimensionC], [DimensionD], [SquareFeet]
)
VALUES
(
1.23 ,1.23,146.23 ,156.23 ,13.23
)
BUT I GOT ERROR WHILE CREATING THE TABLE
Incorrect syntax near '('.
Look with eye at your CREATE TABLE and my CREATE TABLE. You will find your problem.
June 11, 2012 at 1:27 pm
AND ALSO TELL ME IF I ADD ANOTHER COLUMN AS VAR CHAR (10) DATATYPE
CREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[PtId] VARCHAR(10) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal]((18, 4)) NOT NULL,
[DimensionC] [decimal](4, 4)) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL
)
June 11, 2012 at 1:29 pm
1) Turn the caps lock off.I don't appreciate being shouted at.
Yes, that will work once you fix the duplicate brackets. There's a line where you have (( and )) instead of ( and )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 1:31 pm
sivag (6/11/2012)
AND ALSO TELL ME IF I ADD ANOTHER COLUMN AS VAR CHAR (10) DATATYPECREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[PtId] VARCHAR(10) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal]((18, 4)) NOT NULL,
[DimensionC] [decimal](4, 4)) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL
)
To spell it out for you, this:
CREATE TABLE [dbo].[tblPlotInfo](
[ProjectId] [int] IDENTITY(1,1) NOT NULL,
[PtId] VARCHAR(10) NOT NULL,
[DimensionA] [decimal](4, 2) NOT NULL,
[DimensionB] [decimal](18, 4) NOT NULL,
[DimensionC] [decimal](4, 4) NOT NULL,
[DimensionD] [decimal](4, 2) NOT NULL,
[ActualPrice] [decimal](18, 2) NULL
)
June 11, 2012 at 1:53 pm
thanks Lynn Pettis ; makes the difference
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply