CODEWITHSUNDEEP
sqlsql-servertemp-tables
BlackCat
BlackCat

Reputation: 2044

How to use temp table in SQL Server script?

I have to use some hardcode data in sql script, for this I am using temp table 

CREATE TABLE #Temp
(
    [Type] INT,
    StartDate DATETIME,
    EndDate DATETIME,
    InterestRate DOUBLE
);

INSERT INTO #Temp 
VALUES (1, '2015-07-01', '2017-05-01', 27),
       (1, '2017-05-02', '2017-06-30', 25.71)

On line 6, it is showing error as Expecting Id

Is there any simplest way to do this?

Upvotes: 0

Views: 72

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You should use decimal, but you need to give a scale and precision. The default is 0 . . . which is not so useful for an interest rate. See this example.

CREATE TABLE #Temp (
    [Type] INT,
    StartDate DATETIME,
    EndDate DATETIME,
    InterestRate DECIMAL(10, 6)
);

INSERT INTO #Temp 
    VALUES (1, '2015-07-01', '2017-05-01', 27),
           (1, '2017-05-02', '2017-06-30', 25.71);

In addition, SQL Server does have floating point types. They are called float and real. For financial purposes, I think decimal/numeric is a better choice.

Upvotes: 0

Ajeet Verma
Ajeet Verma

Reputation: 1123

SQL doesn't support DOUBLE ,you can use float or decimal instead of DOUBLE.

CREATE TABLE #Temp
(
    [Type] Int,
    StartDate DateTime,
    EndDate DateTime,
    InterestRate decimal
);

Insert Into #Temp Values 
(1,'2015-07-01','2017-05-01',27),
(1,'2017-05-02','2017-06-30',25.71)

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

There is no double data type in sql server use decimal.

CREATE TABLE #Temp
(
    [Type] Int,
    StartDate DateTime,
    EndDate DateTime,
    InterestRate decimal
);

Insert Into #Temp Values 
(1,'2015-07-01','2017-05-01',27),
(1,'2017-05-02','2017-06-30',25.71)

Upvotes: 1

Related Questions