不用循环，如何用一条命令把一个表中的每条记录重复3次写入新表

原表

a b


a b
c d
e f
g h

新表

a b


a b
a b
a b
c d
c d
c d
e f
e f
e f
g h
g h
g h

SQL code

create table t1(id int,col varchar(10))
insert into t1 select 1,'aaa'
insert into t1 select 2,'bbb'
insert into t1 select 3,'ddd'
insert into t1 select 4,'hhh'
go
insert into t1 select * from t1
go 2
select * from t1
/*
id          col
----------- ----------
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh

(16 行受影响)

*/
drop table t1

写入新表:
SQL code

create table t1(id int,col varchar(10))
insert into t1 select 1,'aaa'
insert into t1 select 2,'bbb'
insert into t1 select 3,'ddd'
insert into t1 select 4,'hhh'
create table t2(id int,col varchar(10))
go
insert into t2 select * from t1
go 3
select * from t2
/*
id          col
----------- ----------
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh

(12 行受影响)

*/
drop table t1

如果不允许用go n
则:
SQL code

create table t1(id int,col varchar(10))
insert into t1 select 1,'aaa'
insert into t1 select 2,'bbb'
insert into t1 select 3,'ddd'
insert into t1 select 4,'hhh'
create table t2(id int,col varchar(10))
go
insert into t2 
select * from t1
union all
select * from t1
union all
select * from t1
go
select * from t2
/*
id          col
----------- ----------
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh
1           aaa
2           bbb
3           ddd
4           hhh

(12 行受影响)

*/
drop table t1,t2

SQL code

这样用

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Col1] nvarchar(1),[Col2] nvarchar(1))
Insert #T
select N'a',N'b' union all
select N'a',N'b' union all
select N'c',N'd' union all
select N'e',N'f' union all
select N'g',N'h'
Go
Select a.* 
from #T as a,
(select top 3 ID from sysobjects) as b
/*
Col1    Col2
a    b
a    b
c    d
e    f
g    h
a    b
a    b
c    d
e    f
g    h
a    b
a    b
c    d
e    f
g    h
*/