从一个表动态插入另一个表

Trying to insert records from one table into another. The destination table (@TableName) has three extra columns than the source table (@Sourcetable). These three columns are float columns and are set to NULL.

尝试将记录从一个表插入另一个表。目标表(@TableName)有三个额外的列而不是源表(@Sourcetable)。这三列是浮点列,设置为NULL。

It doesn't matter if a zero is inserted or the NULL is left as is, as long as the insert works

只要插入有效,无论是插入零还是保留NULL都无关紧要

The procedure fails because of that and gives the following error

该过程因此失败,并给出以下错误

Column name or number of supplied values does not match table definition.

列名或提供的值数与表定义不匹配。

See procedure below, any comments how I can do this with the procedure below:

请参阅以下过程,以下操作步骤的任何注释:

ALTER PROCEDURE [dbo].[usr_INSERTRECORD]
    -- Add the parameters for the stored procedure here
    @SourceTable SYSNAME,
    @TableName SYSNAME,
    @TransDate Date,
    @Symbol nvarchar(50)
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @DATEVARCHAR nvarchar(4000);
    SET @DATEVARCHAR = CONVERT(NVARCHAR, @TransDate, 103);

-- Actual Statement that does the insert
-- This is the statement causing the issue; works if schema matches on both tables      
DECLARE @SQLCommand NVARCHAR(MAX) = 
            N'INSERT INTO ' + QUOTENAME(@TableName) + ' SELECT * FROM ' + + QUOTENAME(@SourceTable) 
            + ' WHERE (TRANSDATE = @TransDate AND SYMBOL = @SYMBOL)';

EXECUTE [dbo].[sp_executesql]  @sqlCommand,
                               N'@TransDate date, @Symbol nvarchar(50)',
                               @TransDate = @TransDate, @Symbol = @Symbol;
END

2 个解决方案

#1

You cannot use the asterisk to include all fields if the table definitions are not identical:

如果表定义不相同,则不能使用星号包括所有字段:

You must use something like

你必须使用类似的东西

INSERT INTO table1 (col1, col2, col3, ...) SELECT col1, col2, col3, ... FROM table2

As you are trying to fill the tables generically you could try to create their column lists like this

当您尝试通常填充表格时,您可以尝试创建这样的列列表

SELECT STUFF(
(
SELECT ',' + QUOTENAME(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='YourTableName'
FOR XML PATH('')),1,1,'')

This column list you have to include into your statement...

您必须在您的对帐单中包含此列列表...

#2

If you don't have the same columns, then you have to explicitly list them. If the target table has more, then you can just list them in the insert side, if source table has more, then in the select, but best would be to list them in both.

如果您没有相同的列,则必须明确列出它们。如果目标表有更多,那么你可以在插入端列出它们,如果源表有更多,那么在select中,但最好是在两者中列出它们。

You can also use views for both select and insert, so that the number of columns will match, but anyhow having insert + select * without specifying columns isn't something that I would recommend, because it will break if someone adds new columns into the tables.

你也可以使用select和insert的视图,这样列的数量就会匹配,但无论如何有插入+ select *而不指定列都不是我推荐的,因为如果有人在列中添加新列,它会破坏表。

#1