CODEWITHSUNDEEP
sql-server-2012sql-insert
Coding_Newbie
Coding_Newbie

Reputation: 365

Insert Data into an existing SQL Server table

I'm having problems inserting data into an existing SQL Server table.

IF OBJECT_ID('Coinmarketcap.dbo.tbl_ATR', 'U') IS NOT NULL 
    DROP TABLE Coinmarketcap.dbo.tbl_ATR;   

SELECT
    [Ticker],[Date],[px_high], [px_low], [px_close],[yest_close],
    ATR_spd = iif([px_high] > yest_close, [px_high], yest_close) - iif([px_low] > yest_close, yest_close, [px_low]),
    ATR1 = (iif([px_high] > yest_close, [px_high], yest_close) - iif([px_low] > yest_close, yest_close, [px_low])) / (iif(yest_close = 0 , 1 , yest_close))
FROM 
    (SELECT 
         *, yest_close = lag([px_close]) OVER (PARTITION BY Ticker ORDER BY [Date])
     FROM 
         [Coinmarketcap].[dbo].[daily_data]) t
INTO 
    [Coinmarketcap].[dbo].[tbl_ATR]
ORDER BY 
    [Ticker] ASC, [Date] DESC

I cannot figure out where to place the INTO statement.

Incorrect syntax near the keyword 'INTO'.

Also tried INSERT INTO statement to no avail.

Upvotes: 2

Views: 65

Answers (1)

S3S
S3S

Reputation: 25112

Just a little off... and you don't need an order by since you are dropping the table and it's stored as an unordered set (since you don't have indexes applied here). I assume you want to inherit the definition from the results. This is by using SELECT ... INTO TABLE ... FROM TABLE as opposed to INSERT INTO TABLE ... SELECT ... FROM TABLE

IF OBJECT_ID('Coinmarketcap.dbo.tbl_ATR', 'U') IS NOT NULL 
DROP TABLE Coinmarketcap.dbo.tbl_ATR; 

SELECT
    [Ticker],
    [Date],
    [px_high], 
    [px_low], 
    [px_close],
    [yest_close],
    ATR_spd = iif([px_high] > yest_close, [px_high], yest_close) - iif([px_low] > yest_close, yest_close, [px_low]),
    ATR1 = (iif([px_high] > yest_close, [px_high], yest_close) - iif([px_low] > yest_close, yest_close, [px_low])) / (iif(yest_close = 0 , 1 , yest_close))
INTO [Coinmarketcap].[dbo].[tbl_ATR]
FROM (
    SELECT 
        *, 
        yest_close = lag([px_close]) OVER (PARTITION BY Ticker ORDER BY [Date])
    FROM [Coinmarketcap].[dbo].[daily_data]
) t

Note, you can't do...

INSERT INTO TABLE
SELECT FROM SomeTable

Without first explicitly creating that table. I used SELECT INTO above since you are dropping the table prior to the statement.

Upvotes: 1

Related Questions