Learner44 (7/2/2013)
Hello friends,I am executing bunch of Insert satements as follows:
use Testing
go
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.Students;
.
.
.
.
.
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.teachers;
I have 1450 insert statments, which I am running in SQL SERVER to insert the data from "Linked Server" to "SQL Server".
when I run them in one go ..it give me few successfully inserted records. around 250 out of 1450 and following error
The OLE DB provider "OraOLEDB.Oracle" for linked server "10.198.68.39" supplied inconsistent metadata for a column. The column "NOTESTEXT" (compile-time ordinal 3) of object ""dbo"."Notes"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.
But, When I run them individually or in samll bunch around 23-30 at a time it success fully executes.
I don't know how to get rid of this, because, every time it is very costly to run them individually or in small bunch.
please help.
Thanks.
The first thing I'd try is to combine your 1450 INSERTs into one result set on the remote server.
INSERT INTO Table_Update(TableName,StartlastWritten)
SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten
FROM [10.198.68.39]..dbo.Students;
UNION ALL
.
. -- Next 1448 SELECTs
.
UNION ALL
SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten
FROM [10.198.68.39]..dbo.teachers;[/b]
No guarantees that will help but if you were to compare speed of 1450 inserts on a local server (no remote server) vs. 1 INSERT that combines all 1450 tables I'm sure the difference in speed would be quite apparent.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St