用于插入不同数据类型和列的动态SQL [英] dynamic sql for insertion with different datatypes and columns

问题描述

我想用动态sql语句插入数据库表
具有不同表结构的
以下是第4列和指定的数据类型的示例代码，

i want to insert into database table with dynamic sql statement
with different table structure
below is sample code for columns 4 and datatypes specified ,

insert into table1 select convert(CHAR(8000),col1)as col1, convert(int,cast(col2 as binary)) as col2,
convert(tinyint,cast(col3 as binary)) as col3,
convert(bigint,cast(col4 as binary)) as col4 from table2

我可以将数据类型和列名作为参数aslo传递
怎么可能，请plz在此过程中引导我

i can pass data type and col name as parameter aslo
how is it possible ,plz guide me in this process

推荐答案

使用
中的任何一个
INSERT INTO SELECT或SELECT INTO TABLE

看看这个链接

use any one of

INSERT INTO SELECT or SELECT INTO TABLE

Look at this link

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/[^]

Thanks
SP

如上所述，您可以使用SELECT ... INTO ...结构创建新表.有关文档，请参阅: INTO子句(Transact-SQL) [

As said you can create a new table using SELECT ... INTO ... structure. For documentation, see: INTO Clause (Transact-SQL)[^].

When the table is created the new column names and datatypes are created based on the result set column names and data types as defined in the SELECT portion.

If the table is predefined (already existing), you cannot change the names or the data types of the columns on-the-fly on row basis. In this scenario (using insert into ... select) the column returned by the select must be the same datatype (or implicitly convertible) to the data types defined in the table. Also note that the columns returned by the select statement are inserted into the columns defined in the insert clause based on the ordinal of the column. Meaning that the first column from the query goes to the first column in the column list of the insert statement and so on.

这篇关于用于插入不同数据类型和列的动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！