insert into T1 (col1,col2... ) select col1,col2... from T2
哦? N_Chow(一劍飄香) 你可不要乱讲,呵呵
N_Chow(一劍飄香): 想听听您的思路
N_Chow(一劍飄香) 你要看清楚再答啊,我向你学习!
其实你可以在插入后重新生成. SET IDENTITY_INSERT T1 ON GO Insert Into T1 Select * From T2 DBCC CHECKIDENT (T1, RESEED)
blackhawk_yps(原来是这样) : 我沒亂講。這其實是一個很古老的問題,很久前就已經有討論過了。 T1跟T2的結構若是一樣,而且每一個欄位都是對應的話,就可以采用Dynamic SQL. 這樣通用性會很好。SQL Server 2K: Declare @Col_table1 nvarchar(4000) ,@Col_Table2 nvarchar(4000) ,@DySQL nvarchar(4000) SELECT @Col_table1='',@Col_Table2=''SELECT @Col_table1=@Col_table1+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1' AND COLUMNPROPERTY(Object_ID('table1'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION SELECT @Col_table2=@Col_table2+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table2' AND COLUMNPROPERTY(Object_ID('table2'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION SELECT @Col_Table1=LEFT(@Col_Table1,LEN(@Col_Table1)-1),@Col_Table2=LEFT(@Col_Table2,LEN(@Col_Table2)-1) SET @DySQL=' INSERT INTO table1 (' +@Col_Table1+') SELECT '+@Col_Table2 +' From table2 ' EXEC (@DySQL)
建临时表 语法如下: Create temporary table tbl_name type=heap select * from table1; insert into ... select ... from table1 ... insert into ... select ... from table2 ... select * from tbl_name ... drop tbl_name
就可以了
SET IDENTITY_INSERT T1 ON
GO
Insert Into T1 Select * From T2
DBCC CHECKIDENT (T1, RESEED)
T1跟T2的結構若是一樣,而且每一個欄位都是對應的話,就可以采用Dynamic SQL.
這樣通用性會很好。SQL Server 2K:
Declare @Col_table1 nvarchar(4000)
,@Col_Table2 nvarchar(4000)
,@DySQL nvarchar(4000)
SELECT @Col_table1='',@Col_Table2=''SELECT @Col_table1=@Col_table1+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1' AND COLUMNPROPERTY(Object_ID('table1'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION
SELECT @Col_table2=@Col_table2+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table2' AND COLUMNPROPERTY(Object_ID('table2'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION
SELECT @Col_Table1=LEFT(@Col_Table1,LEN(@Col_Table1)-1),@Col_Table2=LEFT(@Col_Table2,LEN(@Col_Table2)-1)
SET @DySQL=' INSERT INTO table1 (' +@Col_Table1+') SELECT '+@Col_Table2 +' From table2 ' EXEC (@DySQL)
不过楼主会觉得太复杂,信吗?
你的语句中还有一点小问题,最后一个','需要去掉
以后一定要多多指教啊,光一句动态sql看不懂啊。
再请教你,这样 的问题在实际应用中多吗?这样的解决方法完善吗?
语法如下:
Create temporary table tbl_name type=heap select * from table1;
insert into ... select ... from table1 ...
insert into ... select ... from table2 ...
select * from tbl_name ...
drop tbl_name
呵呵,在我的程式中,從未用到過這種功能。就連動態SQL也從未用到過。
另,上面的","已經去掉了。