每天用另一个 SQL 填充一个表 [英] Daily populating a table by another one SQL

问题描述

我想用另一个表填充一个表(之后会被截断)，但是当我使用它时，它只是一次，因为 SQL 说该表已经存在.我只想在 Alberta 表中添加行.

I want to populate a table by another one (which will get truncate after), however when I used this it's only a ONE shot because SQL says that the table exist already. I just want to add rows into that Alberta table.

USE [DATAWAREHOUSE]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[prcAlberta] As 

(
Select *
INTO AlbertaData_Storing
FROM dbo.AlbertaData_import
where name is not null )

truncate table AlbertaData_import
GO

推荐答案

由于命令 insert all into 'TableName' from another table create a new table with schema，因此发生错误.所以需要像这样更改查询.

Error occurred because command insert all into 'TableName' from another table create a new table with schema. So that need to change the query like this.

USE [DATAWAREHOUSE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prcAlberta] 
AS 
BEGIN
    /* If the table not exist then create a blank table */
    IF OBJECT_ID('AlbertaData_Storing') IS NULL
    BEGIN
        SELECT * INTO AlbertaData_Storing
        FROM  dbo.AlbertaData_import WHERE 1 <> 1
    END
    /* 
    Better to use required column only Like :
    INSERT INTO AlbertaData_Storing(Column1,Column2,Column3,...)
    SELECT Column1,Column2,Column3,... FROM dbo.AlbertaData_import
    WHERE Name IS NOT NULL
    */
    INSERT INTO AlbertaData_Storing
    SELECT * FROM dbo.AlbertaData_import
    WHERE Name IS NOT NULL

    TRUNCATE TABLE AlbertaData_import
END                       
GO

这篇关于每天用另一个 SQL 填充一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！