*/ALTER Proc [dbo].[spInsertSql] ( @TableName varchar(100), @DataCon varchar(Max) ) as Set nocount on declare @sql varchar(Max) declare @sqlValues varchar(Max) set @sql =' (' set @sqlValues = 'Values (''+' select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],' from (select case when xtype in (35,48,52,56,59,60,62,104,106,108,122,127) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else ' + 'cast(['+ a.name + '] As Varchar)'+' End' when xtype in (58,61) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast(['+ a.name +'] As Varchar)'+ '+'''''''''+' End' when xtype in (167) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Replace(['+ a.name+'],'''''''','''''''''''')' + '+'''''''''+' End' when xtype in (231) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Replace(['+ a.name+'],'''''''','''''''''''')' + '+'''''''''+' End' when xtype in (175) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast(Replace(['+ a.name+'],'''''''','''''''''''') As Char(' + cast(length as varchar) + '))+'''''''''+' End' when xtype in (239) then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Cast(Replace(['+ a.name+'],'''''''','''''''''''') As Char(' + cast(length as varchar) + '))+'''''''''+' End' else '''NULL''' end as Cols,a.name from syscolumns a Inner Join sys.columns b On a.id = b.object_id and a.name = b.name where b.is_identity = 0 and a.id = object_id(@tablename) --And (xtype <> 56 And typestat <> 1 And colstat <> 1) --排除Identity类型列 ) T Create Table #temp(selectsql varchar(Max)) Set @sql ='insert into #temp Select ''Insert Into ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' From '+@tablename Set @Sql = @Sql + ' ' + @DataCon
exec (@sql) if Exists(Select 1 From #temp) begin print 'Delete ' + @TableName + ' ' + @DataCon end
DECLARE Table_Cursor CURSOR FOR Select selectsql From #temp
Declare @inserttable varchar(Max) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor Into @inserttable WHILE @@FETCH_STATUS >= 0 Begin print @inserttable FETCH NEXT FROM Table_Cursor Into @inserttable End print ' Go' Close Table_Cursor OPEN Table_Cursor Drop Table #temp
选中表---详细选目标表---下一步直到完成。
请问这样写可以一次在表A中插入两行数据吗?
就是:
a b c
1 2 3
4 5 6请问,有这种语法吗?
楼主可以装个PB,然后建个datawindow,然后把它save row as,选择SQL的格式,就可以出来了,下面是我得到的结果:
CREATE TABLE 1 (
id decimal(16, 0),
typeid decimal(16, 0),
classid decimal(16, 0),
consumeno char(10),
consumecname char(50),
pycode char(50),
isdisc char(1),
isservice char(1),
isrename char(1),
issuit char(1),
isalterprice char(1),
orderno char(14),
outflags char(6),
backcolor char(1));
INSERT INTO 1 VALUES (
1,
1,
1,
'0101001',
'餐 费',
'CF',
'1',
'1',
'0',
'0',
'1',
'010100100',
'',
'');
INSERT INTO 1 VALUES (
2,
1,
1,
'0101002',
'自助餐',
'ZZC',
'1',
'1',
'0',
'0',
'1',
'010100200',
'',
'');
INSERT INTO 1 VALUES (
358,
1,
1,
'0101003',
'测试套餐',
'CSTC',
'1',
'1',
'0',
'1',
'1',
'01010030000',
'',
'');
INSERT INTO 1 VALUES (
3,
1,
2,
'0102001',
'风情牛肉脯',
'FQNRF',
'1',
'1',
'0',
'0',
'0',
'010200100',
'',
'');
INSERT INTO 1 VALUES (
4,
1,
2,
'0102002',
'顺风猪耳尖',
'SFZEJ',
'1',
'1',
'0',
'0',
'1',
'010200200',
'已沽清',
'');
INSERT INTO 1 VALUES (
5,
1,
2,
'0102003',
'醋椒海蜇头',
'CJHZT',
'1',
'1',
'0',
'0',
'1',
'010200300',
'已沽清',
'');
INSERT INTO 1 VALUES (
6,
1,
2,
'0102004',
'徽味卤酥腰',
'HWLSY',
'1',
'1',
'0',
'1',
'1',
'010200400',
'',
'');
INSERT INTO 1 VALUES (
7,
1,
2,
'0102005',
'川味童子鸡',
'CWTZJ',
'1',
'1',
'0',
'0',
'1',
'010200500',
'',
'');
INSERT INTO 1 VALUES (
8,
1,
2,
'0102006',
'酱皇烤鸭舌',
'JHKYS',
'1',
'1',
'0',
'0',
'1',
'010200600',
'已沽清',
'');
INSERT INTO 1 VALUES (
9,
1,
2,
'0102007',
'豉香手撕兔',
'CXSST',
'1',
'1',
'0',
'0',
'1',
'010200700',
'已沽清',
'');
INSERT INTO 1 VALUES (
10,
1,
2,
'0102008',
'卤水金钱肚',
'LSJQD',
'1',
'1',
'0',
'0',
'1',
'010200800',
'',
'');
INSERT INTO 1 VALUES (
11,
1,
2,
'0102009',
'凉瓜拌鱼皮',
'LGBYP',
'1',
'1',
'0',
'0',
'1',
'010200900',
'',
'');
/* 根据一个库中的数据生成Insert语句
*/ALTER Proc [dbo].[spInsertSql]
(
@TableName varchar(100),
@DataCon varchar(Max)
)
as Set nocount on
declare @sql varchar(Max)
declare @sqlValues varchar(Max)
set @sql =' ('
set @sqlValues = 'Values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (35,48,52,56,59,60,62,104,106,108,122,127)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else ' + 'cast(['+ a.name + '] As Varchar)'+' End'
when xtype in (58,61)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast(['+ a.name +'] As Varchar)'+ '+'''''''''+' End'
when xtype in (167)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Replace(['+ a.name+'],'''''''','''''''''''')' + '+'''''''''+' End'
when xtype in (231)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Replace(['+ a.name+'],'''''''','''''''''''')' + '+'''''''''+' End'
when xtype in (175)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+''''''''' + ' + 'Cast(Replace(['+ a.name+'],'''''''','''''''''''') As Char(' + cast(length as varchar) + '))+'''''''''+' End'
when xtype in (239)
then 'Case When ['+ a.name +'] Is Null Then ''NULL'' Else '+'''N'''''' + ' + 'Cast(Replace(['+ a.name+'],'''''''','''''''''''') As Char(' + cast(length as varchar) + '))+'''''''''+' End'
else '''NULL'''
end as Cols,a.name
from syscolumns a
Inner Join sys.columns b On a.id = b.object_id and a.name = b.name
where b.is_identity = 0 and a.id = object_id(@tablename) --And (xtype <> 56 And typestat <> 1 And colstat <> 1) --排除Identity类型列
) T
Create Table #temp(selectsql varchar(Max))
Set @sql ='insert into #temp Select ''Insert Into ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' From '+@tablename
Set @Sql = @Sql + ' ' + @DataCon
exec (@sql)
if Exists(Select 1 From #temp)
begin
print 'Delete ' + @TableName + ' ' + @DataCon
end
DECLARE Table_Cursor CURSOR FOR
Select selectsql
From #temp
Declare @inserttable varchar(Max)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor Into @inserttable
WHILE @@FETCH_STATUS >= 0
Begin
print @inserttable
FETCH NEXT FROM Table_Cursor Into @inserttable
End
print ' Go'
Close Table_Cursor
OPEN Table_Cursor
Drop Table #temp
Set nocount off