hVosttА триггеры, процедуры и прочая ересь, это однозначно бууэээ... Никогда так не делай. Никогда.
Почему? В данном случае (BulkCopy в N таблиц из 1 источника) это (вью+триггер) самое простое и эффективное решение, имхо конечно.
Вью можно сделать лишь "оберткой" для заливаемых данных, т.е., не основывая на каких-либо таблицах. Вот простецкий пример.
t-sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
if object_id('dbo.tblA') is not null drop table dbo.tblA
go
create table dbo.tblA(
ID int not null constraint PK_tblA primary key
,name varchar(100) not null
)
go
if object_id('dbo.tblB') is not null drop table dbo.tblB
go
create table dbo.tblB(
ID int not null constraint PK_tblB primary key
,name varchar(100) not null
)
go
if object_id('dbo.vw4BulkCopy') is not null drop view dbo.vw4BulkCopy
go
create view dbo.vw4BulkCopy
as
select 0 as [ID], cast('name1' as varchar(100)) as [name1], cast('name2' as varchar(100)) as [name2]
go
create trigger dbo.trInsVw4BulkCopy on dbo.vw4BulkCopy instead of insert
as
begin
insert into dbo.tblA select [ID], [name1] from inserted
insert into dbo.tblB select [ID], [name2] from inserted
end
go
c#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApp3
{
class Program
{
static string connectionString = @"Data Source=.;Initial Catalog=testdb;Integrated Security=True;";
static void Main(string[] args)
{
TestBulkCopy(1000);
Console.ReadKey();
}
static void TestBulkCopy(int recordscount)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn,
SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
//bulkCopy.ColumnMappings.
bulkCopy.DestinationTableName = "dbo.vw4BulkCopy";
using (TestBulkCopyReader reader = new TestBulkCopyReader(recordscount))
{
bulkCopy.WriteToServer(reader);
Console.WriteLine($"Donecount={reader.Donecount - 1}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public class TestBulkCopyReader : IDataReader
{
int _recordscount = 0;
public int Donecount = 0;
public TestBulkCopyReader(int recordscount)
{
_recordscount = recordscount;
}
#region IDataReader members
public bool Read()
{
return ++Donecount <= _recordscount;
}
public object GetValue(int i)
{
switch (i)
{
case 0: return Donecount; //ID1
case 1: return string.Format($"row {Donecount} for table A");
case 2: return string.Format($"row {Donecount} for table B");
default: return null;
}
}
public int FieldCount
{
get { return 3; }
}
public void Dispose()
{
}
public string GetName(int i)
{
throw new NotImplementedException();
}
public int GetOrdinal(string name)
{
throw new NotImplementedException();
}
public void Close()
{
throw new NotImplementedException();
}
public int Depth
{
get { throw new NotImplementedException(); }
}
public DataTable GetSchemaTable()
{
throw new NotImplementedException();
}
public bool IsClosed
{
get { throw new NotImplementedException(); }
}
public bool NextResult()
{
throw new NotImplementedException();
}
public int RecordsAffected
{
get { throw new NotImplementedException(); }
}
public bool GetBoolean(int i)
{
throw new NotImplementedException();
}
public byte GetByte(int i)
{
throw new NotImplementedException();
}
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public char GetChar(int i)
{
throw new NotImplementedException();
}
public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public IDataReader GetData(int i)
{
throw new NotImplementedException();
}
public string GetDataTypeName(int i)
{
throw new NotImplementedException();
}
public DateTime GetDateTime(int i)
{
throw new NotImplementedException();
}
public decimal GetDecimal(int i)
{
throw new NotImplementedException();
}
public double GetDouble(int i)
{
throw new NotImplementedException();
}
public Type GetFieldType(int i)
{
throw new NotImplementedException();
}
public float GetFloat(int i)
{
throw new NotImplementedException();
}
public Guid GetGuid(int i)
{
throw new NotImplementedException();
}
public short GetInt16(int i)
{
throw new NotImplementedException();
}
public int GetInt32(int i)
{
throw new NotImplementedException();
}
public long GetInt64(int i)
{
throw new NotImplementedException();
}
public string GetString(int i)
{
throw new NotImplementedException();
}
public int GetValues(object[] values)
{
throw new NotImplementedException();
}
public bool IsDBNull(int i)
{
throw new NotImplementedException();
}
public object this[string name]
{
get { throw new NotImplementedException(); }
}
public object this[int i]
{
get { throw new NotImplementedException(); }
}
#endregion
}
}
}
