reduce databse size: insert

Showing posts with label insert. Show all posts

Friday, March 30, 2012

Regarding Data Transpose

Hello,
I have got a table, which consists 5 columns. If suppose there are ten
rows, then I want to insert the data present in this table into another
temporary table, which consists of 50 columns. So, effectively I want to
convert all the rows into one row by transposing. How can I do this?
--With Regards,
Sheshadrinath.R"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:37037772-7988-45E4-B957-62439844EFD8@.microsoft.com...
> Hello,
> I have got a table, which consists 5 columns. If suppose there are ten
> rows, then I want to insert the data present in this table into another
> temporary table, which consists of 50 columns. So, effectively I want to
> convert all the rows into one row by transposing. How can I do this?
> --With Regards,
> Sheshadrinath.R
Why? Have you considered just changing the way you display the data rather
than attempting such a thing in the database?
You haven't given enough information to answer your question fully. How
should we determine which rows get transposed to which columns for example?
Google this group for "transpose" and "crosstab" and you'll find plenty of
examples that might help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas

"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql

Regarding BULK insert

I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..
chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Regarding BULK insert

I am doing a bulk insert and i am A DBO on the database and i can't do it u
nless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it
unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Wednesday, March 28, 2012

Reg:Tables data changes auditing

Hi,

I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.

One way is, I have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write like that.

Is thesre any other solution to find the updated,inserted tables when data is changing from application without doing bulk changes.

Regards

Hanu

unfortunately SQL Server can not do this until unless we have our own mechanism.

Madhu

Reg: Table data changes auditing

Hi,

I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.

One way is i have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write.

Is thesre any other way to find the updated,inserted tables when data is changing from application.

Regards

Hanu

hi,

You may get more help posting it in

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

SQL Server Database Engine forum

Although i maybe wrong

Good luck

Matt

Monday, March 26, 2012

reg Bulk Insert

Hi,
I have a query reg the bulk insert. I have some data in text file with
tab delimetered and using BULK INSERT to get this data into a phycical table
.
i have more columns in the table than in the file. Table is having 10
columns and text file is having only 8 columns.
i am getting the below error ..
'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
Is there any option for Bulk insert to specify about the number columns to
be inserted in the table?
Thanks in advance
BhaskarYou can do this by specifying a FORMATFILE on your BULK INSERT statement.
The format file describes the fields in the text file and which are to be
imported. See 'Using Format Files' the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
> Hi,
> I have a query reg the bulk insert. I have some data in text file with
> tab delimetered and using BULK INSERT to get this data into a phycical
> table.
> i have more columns in the table than in the file. Table is having 10
> columns and text file is having only 8 columns.
> i am getting the below error ..
> 'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
> Is there any option for Bulk insert to specify about the number columns to
> be inserted in the table?
> Thanks in advance
> Bhaskar|||Hi ,
Thanks for the reply. Is there any example with anybody about how to create
the Format file..
Thanks
Bhaskar
"Dan Guzman" wrote:

> You can do this by specifying a FORMATFILE on your BULK INSERT statement.
> The format file describes the fields in the text file and which are to be
> imported. See 'Using Format Files' the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
> news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
>
>|||The Books Online has examples of format files with fewer fields than the
destination table as well as with more fields.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:93DFC97E-B9D7-461E-BD17-702996CA0F96@.microsoft.com...
> Hi ,
> Thanks for the reply. Is there any example with anybody about how to
> create
> the Format file..
> Thanks
> Bhaskar
> "Dan Guzman" wrote:
>sql

Tuesday, March 20, 2012

Referencing destination table in INSERT SELECT statement

Hi
I hava a following piece of code:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
As you can see I'm trying to copy data from the source table to the
destination one, and while doing so, assign each row a subsequent
number (d) starting from 0.
I assumed, that the SELECT clause will be run for each inserted row -
however - when I run this code as the result I receive only zeros in
the d column for all rows.
I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
Is there any way to get this query running?
Thanks
Szymon
Hi
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s,(select count(*) from #s s where s.s<=#s.s) FROM
#s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
<joozeq@.gmail.com> wrote in message
news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||Hi,
You can try the following query:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Without creating the #d table you can create and insert the data of #s table
in #d by the following query:
SELECT s, ROWID=IDENTITY(int,0,1) into #d from #s
Regards
Swaprakash
"joozeq@.gmail.com" wrote:

> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||joozeq@.gmail.com wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
Make the column "d" an identity column, and let SQL assign the
incremental value:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int identity(0,1))
INSERT INTO #d SELECT s FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Why not add an IDENTITY column to table #s?
CREATE TABLE #s
( RowID int IDENTITY,
s varchar(20)
)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegro ups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):[vbcol=seagreen]
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegro ups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>

Referencing destination table in INSERT SELECT statement

Hi
I hava a following piece of code:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
As you can see I'm trying to copy data from the source table to the
destination one, and while doing so, assign each row a subsequent
number (d) starting from 0.
I assumed, that the SELECT clause will be run for each inserted row -
however - when I run this code as the result I receive only zeros in
the d column for all rows.
I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
Is there any way to get this query running?
Thanks
SzymonHi
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int)
INSERT INTO #d SELECT s,(select count(*) from #s s where s.s<=#s.s) FROM
#s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
<joozeq@.gmail.com> wrote in message
news:1163408290.886457.14770@.h54g2000cwb.googlegroups.com...
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>|||Hi,
You can try the following query:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
Without creating the #d table you can create and insert the data of #s table
in #d by the following query:
SELECT s, ROWID=IDENTITY(int,0,1) into #d from #s
Regards
Swaprakash
"joozeq@.gmail.com" wrote:

> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>|||joozeq@.gmail.com wrote:
> Hi
> I hava a following piece of code:
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> CREATE TABLE #d(s varchar, d int)
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
> Is there any way to get this query running?
> Thanks
> Szymon
>
Make the column "d" an identity column, and let SQL assign the
incremental value:
CREATE TABLE #s(s varchar)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
CREATE TABLE #d(s varchar, d int identity(0,1))
INSERT INTO #d SELECT s FROM #s
SELECT d FROM #d
DROP TABLE #d
DROP TABLE #s
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Why not add an IDENTITY column to table #s?
CREATE TABLE #s
( RowID int IDENTITY,
s varchar(20)
)
INSERT INTO #s VALUES ('a')
INSERT INTO #s VALUES ('b')
INSERT INTO #s VALUES ('c')
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegroups.co
m...
> Hi
>
> I hava a following piece of code:
>
> CREATE TABLE #s(s varchar)
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
>
> CREATE TABLE #d(s varchar, d int)
>
> INSERT INTO #d SELECT s, ISNULL((SELECT MAX(d) + 1 FROM #d), 0) FROM #s
>
> SELECT d FROM #d
> DROP TABLE #d
> DROP TABLE #s
>
> As you can see I'm trying to copy data from the source table to the
> destination one, and while doing so, assign each row a subsequent
> number (d) starting from 0.
>
> I assumed, that the SELECT clause will be run for each inserted row -
> however - when I run this code as the result I receive only zeros in
> the d column for all rows.
>
> I've already tried WITH (NOLOCK/ROWLOCK) clauses to no avail.
>
> Is there any way to get this query running?
>
> Thanks
> Szymon
>|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):[vbcol=seagreen]
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/defaul...kb;en-us;186133
http://sqljunkies.com/WebLog/amacha...11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegroups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>

Referencing destination table in INSERT SELECT statement

Why not add an IDENTITY column to table =#s?
CREATE TABLE #s =( RowID int IDENTITY, s varchar(20) )
INSERT INTO #s VALUES =('a')INSERT INTO #s VALUES ('b')INSERT INTO #s VALUES ('c')
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
wrote in message news:1163408290.886457.14770@.h54g2000cwb.googlegroups.com=...> =Hi> > I hava a following piece of code:> > CREATE TABLE =#s(s varchar)> INSERT INTO #s VALUES ('a')> INSERT INTO #s =VALUES ('b')> INSERT INTO #s VALUES ('c')> > CREATE TABLE =#d(s varchar, d int)> > INSERT INTO #d SELECT s, ISNULL((SELECT =MAX(d) + 1 FROM #d), 0) FROM #s> > SELECT d FROM #d> DROP =TABLE #d> DROP TABLE #s> > As you can see I'm trying to =copy data from the source table to the> destination one, and while doing =so, assign each row a subsequent> number (d) starting from 0.> => I assumed, that the SELECT clause will be run for each inserted row =-> however - when I run this code as the result I receive only zeros =in> the d column for all rows.> > I've already tried WITH =(NOLOCK/ROWLOCK) clauses to no avail.> > Is there any way to get this query =running? > > Thanks> =Szymon>

--=_NextPart_000_00FF_01C706FC.3EEA82B0--|||I can't use IDENTITY because the problem is a bit more complex that I
might have described it at the beginning of this thread. Here's a bit
less abstract story:
I have a table with about 150 000 rows, which holds geographical
coordinates of a certain GPS receiver (placed in a vehicle) in a
certain moment. The table structure is as follows:
IdCoordinates int IDENTITY PRIMARY KEY
CarId nvarchar(50)
Longitude float
Latitude float
Date datetime (indexed)
Quite often I need to derive the speed of a car throughout a day,
basing on this data. This involves finding for each row in a day the
preceeding one (to calculate the distance driven and time elapsed
between the two). The simplest way is to construct a query basing on
NOT EXISTS operator but this prooves itself to be terribly slow.
So I figured out I'd add to the table additional column, SequenceNo
int, which for a certain row would hold number of all rows gathered
from the same car with dates lesser the the row's date. This makes the
query both simple and efficient.
However - with 150 000 rows (and very soon I expect it to be over 500
000) - adding this column requires first to properly update the
SequenceNo field for all rows.
First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
MAX ... would do, but this query sets all values to 1 (providing all
the values are NULL at the beginning) - apparently some row/table
locking issue (is it?).
Then I tried to create something like Oracle's sequence that would
return next sequence number for the given car id, but this requires one
of the following:
- SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
table )
- UDF (so I can use it in the SET clause OF the UPDATE query) that can
issue DML statements (so it can remember current seqence number for a
certain car and return the next one)
- SP (they can ofcourse use DML) that can return value and be used in a
SET clause
AFAIK none of these exist in SQL Server 2000 (though there's a hack
somewhere on the web to create a SELECT trigger). So now I've ended up
with a SP that simply reads all data from the Coordinates table with a
cursor (the data being read must be ordered by date), fetches next
available SequenceNo from a temp table and inserts it to another temp
table with the SequenceNo field set properly (I know that updating the
source table would be more appropriate here but it's very slow).
It's dirty, slow and resource consuming - my best solution so far is to
use an update curosor and update the row in place with WHERE CURRENT OF
clause - but apparently SQL Server 2000 doesn't support curors with
ORDER BY and FOR UPDATE clauses put together, so this works only for
SQL Server 2005.
I'd appreciate any suggestions on how to solve this nicely.
Szymon
Arnie Rowland napisal(a):
> Why not add an IDENTITY column to table #s?
> CREATE TABLE #s
> ( RowID int IDENTITY,
> s varchar(20)
> )
> INSERT INTO #s VALUES ('a')
> INSERT INTO #s VALUES ('b')
> INSERT INTO #s VALUES ('c')
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>|||Sorry - I CAN use ordered update cursors with SQL 2000 - I simply
forgot to add PK to the table and got misleading error message.
If anyone knows a better solution to the problem then the one with
update cursor I'd appreciate suggestions.
Szymon
joozeq@.gmail.com napisal(a):
> I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
> > Why not add an IDENTITY column to table #s?
> >
> > CREATE TABLE #s
> > ( RowID int IDENTITY,
> > s varchar(20)
> > )
> >
> > INSERT INTO #s VALUES ('a')
> > INSERT INTO #s VALUES ('b')
> > INSERT INTO #s VALUES ('c')
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> > You can't help someone get up a hill without getting a little closer to the top yourself.
> > - H. Norman Schwarzkopf
> >
> >|||Perhaps one of these articles may help:
Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<joozeq@.gmail.com> wrote in message
news:1164136011.455451.90600@.b28g2000cwb.googlegroups.com...
>I can't use IDENTITY because the problem is a bit more complex that I
> might have described it at the beginning of this thread. Here's a bit
> less abstract story:
> I have a table with about 150 000 rows, which holds geographical
> coordinates of a certain GPS receiver (placed in a vehicle) in a
> certain moment. The table structure is as follows:
> IdCoordinates int IDENTITY PRIMARY KEY
> CarId nvarchar(50)
> Longitude float
> Latitude float
> Date datetime (indexed)
> Quite often I need to derive the speed of a car throughout a day,
> basing on this data. This involves finding for each row in a day the
> preceeding one (to calculate the distance driven and time elapsed
> between the two). The simplest way is to construct a query basing on
> NOT EXISTS operator but this prooves itself to be terribly slow.
> So I figured out I'd add to the table additional column, SequenceNo
> int, which for a certain row would hold number of all rows gathered
> from the same car with dates lesser the the row's date. This makes the
> query both simple and efficient.
> However - with 150 000 rows (and very soon I expect it to be over 500
> 000) - adding this column requires first to properly update the
> SequenceNo field for all rows.
> First I thought that simple UPDATE Coordinates SET SequenceNo = SELECT
> MAX ... would do, but this query sets all values to 1 (providing all
> the values are NULL at the beginning) - apparently some row/table
> locking issue (is it?).
> Then I tried to create something like Oracle's sequence that would
> return next sequence number for the given car id, but this requires one
> of the following:
> - SELECT trigger (I'd create it on a Sequence(CarId, NextSequenceNo)
> table )
> - UDF (so I can use it in the SET clause OF the UPDATE query) that can
> issue DML statements (so it can remember current seqence number for a
> certain car and return the next one)
> - SP (they can ofcourse use DML) that can return value and be used in a
> SET clause
> AFAIK none of these exist in SQL Server 2000 (though there's a hack
> somewhere on the web to create a SELECT trigger). So now I've ended up
> with a SP that simply reads all data from the Coordinates table with a
> cursor (the data being read must be ordered by date), fetches next
> available SequenceNo from a temp table and inserts it to another temp
> table with the SequenceNo field set properly (I know that updating the
> source table would be more appropriate here but it's very slow).
> It's dirty, slow and resource consuming - my best solution so far is to
> use an update curosor and update the row in place with WHERE CURRENT OF
> clause - but apparently SQL Server 2000 doesn't support curors with
> ORDER BY and FOR UPDATE clauses put together, so this works only for
> SQL Server 2005.
> I'd appreciate any suggestions on how to solve this nicely.
> Szymon
> Arnie Rowland napisal(a):
>> Why not add an IDENTITY column to table #s?
>> CREATE TABLE #s
>> ( RowID int IDENTITY,
>> s varchar(20)
>> )
>> INSERT INTO #s VALUES ('a')
>> INSERT INTO #s VALUES ('b')
>> INSERT INTO #s VALUES ('c')
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the top yourself.
>> - H. Norman Schwarzkopf
>>
>

Friday, March 9, 2012

Reference ID problem for import to SQL

I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
all data are insert into tables, but I want a reference element (foreign key
to a Id in a higher level element), that inherits a ID from another element.
I have a regular XML file and a mapping XSD file.
XML file:
<DKMaster>
<continent>
<name>Europa</name>
<country>
<name>Denmark</name>
<location>
<name>Kolding</name>
</location>
............ ' recursive
</country>
............ 'recursive
</continent>
..............' recursive
</DKMaster>
XSD file:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Cont_Loc" parent="continent" parent-key="Id"
child="location" child-key="ContinentId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
<xsd:element name="ContinentId" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I want to reference the Continent/Id to Location/ContinentID, how is this
possible ?
Database structure:
Table Continent
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
Table Location
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
- ContinentId int F.K.
"Magni" wrote:

> I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
> all data are insert into tables, but I want a reference element (foreign key
> to a Id in a higher level element), that inherits a ID from another element.
> I have a regular XML file and a mapping XSD file.
> XML file:
> <DKMaster>
> <continent>
> <name>Europa</name>
> <country>
> <name>Denmark</name>
> <location>
> <name>Kolding</name>
> </location>
> ............ ' recursive
> </country>
> ............ 'recursive
> </continent>
> ..............' recursive
> </DKMaster>
> XSD file:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="Cont_Loc" parent="continent" parent-key="Id"
> child="location" child-key="ContinentId" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> <xsd:element name="ContinentId" type="xsd:integer" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I want to reference the Continent/Id to Location/ContinentID, how is this
> possible ?
|||Hi Magni,
The location element should be defined as a child of the continent element
in the schema.. (see the example below)
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="location" ref="location" sql:relation="Continent"
sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
However I see one more problem though..
Your data has
<continent>
<country>
<location>
In your schema you have not defined the <country> element though.
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Magni" <Magni@.discussions.microsoft.com> wrote in message
news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...[vbcol=seagreen]
> Database structure:
> Table Continent
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> Table Location
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> - ContinentId int F.K.
> "Magni" wrote:
working,[vbcol=seagreen]
key[vbcol=seagreen]
element.[vbcol=seagreen]
recursive[vbcol=seagreen]
sql:relation="Continent"[vbcol=seagreen]
this[vbcol=seagreen]
|||"Chandra Kalyanaraman [MSFT]" wrote:

> Hi Magni,
> The location element should be defined as a child of the continent element
> in the schema.. (see the example below)
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="location" ref="location" sql:relation="Continent"
> sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> However I see one more problem though..
> Your data has
> <continent>
> <country>
> <location>
> In your schema you have not defined the <country> element though.
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Magni" <Magni@.discussions.microsoft.com> wrote in message
> news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...
> working,
> key
> element.
> recursive
> sql:relation="Continent"
> this
>
>
Hi Chandra
I am ahaving a Same kind of Issue
<Student>
<School>
<ApCourse>
</ApCourse>
</School>
</Student>
I am trying to Insert into ApCourse, the value from School --> SchoolID
I tried with your IDea of Using a reference ,but its throwing me a Error
Hi,
I have to Insert into 3 tables data coming as a XML file
DistrictStudent PK--> Auto ID
SchoolStudent PK -->DistrictStudent_AutoId
Tablle is referenced by Foreign key
SchoolStudentAPCourse: FK_SchoolStudentAPCourse_SchoolStudent
SchoolStudentAPCourse PK -->SchoolStudentId
I am able to Insert Data into two tables, but when I want to Insert into the
third table( SchoolStudentAPCourse ), its giving me Error
Here is the Schema formy File
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:mstns="http://tempuri.org/XMLSchema1.xsd">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="DSSS"parent="DistrictStudent"
parent-key="AutoId"
child="SchoolStudent"
child-key="DistrictStudent_AutoId" />
<sql:relationship name="SchoolStudentAPCourse" parent="SchoolStudent"
parent-key="SchoolStudentId"
child="SchoolStudentApCourse"
child-key="SchoolStudentId"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:elementname="Student" sql:relation="DistrictStudent"
sql:key-fields="DataVersionId">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DataVersionId"
type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element name="SSID"type="xsd:int"sql:field="SSID" />
<xsd:element name="SSN"type="xsd:string"sql:field="SSN" />
<xsd:element
name="FirstName"type="xsd:string"sql:field="FirstName"/>
<xsd:element
name="MiddleName"type="xsd:string"sql:field="MiddleName" />
<xsd:element
name="LastName"type="xsd:string"sql:field="LastName"/>
<xsd:element
name="BirthDate"type="xsd:date"sql:field="BirthDate" />
<xsd:element
name="GenderTypeCode"type="xsd:string"sql:field="GenderTypeCode"/>
<xsd:element
name="RaceTypeId"type="xsd:int"sql:field="RaceTypeId" />
<xsd:element
name="DisabililtyTypeId"type="xsd:int"sql:field="DisabilityTypeId"/>
<xsd:element
name="PrimaryLanguageId"type="xsd:int"sql:field="PrimaryLanguageId"/>
<xsd:element
name="LanguageSpokenAtHomeId"type="xsd:int"sql:field="LanguageSpokenAtHomeId"/>
<xsd:element
name="ZipCode"type="xsd:string"sql:field="ZipCode"/>
<xsd:element
name="ZipCodeExt"type="xsd:string"sql:field="ZipCodeExt"/>
<xsd:element
name="ExpectedGradYear"type="xsd:string"sql:field="ExpectedGradYear"/>
<xsd:element name="ResidentSchoolDistrictOrganizationId"
type="xsd:int"sql:field="ResidentSchoolDistrictOrganizationId"/>
<xsd:element
name="PreSchool"type="xsd:int"sql:field="PreSchool"/>
<xsd:element
name="IsHomeless"type="xsd:int"sql:field="IsHomeless"/>
<xsd:element
name="IsForeignExchange"type="xsd:int"sql:field="IsForeignExchange"/>
<xsd:element
name="IsHomeBasedStudentAttendingPartTime"type="xsd:int"sql:field="IsHomeBasedStudentAttendingPartTime"/>
<xsd:element
name="IsApprovedPrivateSchoolStudentAttendingPartT ime"type="xsd:int"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="DateEnrolledInDistrict"type="xsd:date"sql:field="DateEnrolledInDistrict"/>
<xsd:element
name="DateExitedDistrict"type="xsd:date"sql:field="DateExitedDistrict"/>
<xsd:element
name="ELLEnrollmentDate"type="xsd:date"sql:field="ELLEnrollmentDate"/>
<xsd:element
name="ELLExitDate"type="xsd:date"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="GradeLevelId"type="xsd:int"sql:field="GradeLevelId" />
<xsd:element name="GPA"type="xsd:decimal"sql:field="GPA"
/>
<xsd:element
name="FreeReducedMealStatus"type="xsd:int"sql:field="FreeReducedMealStatus" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy"/>
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated"/>
<xsd:element
name="MatchType"type="xsd:string"sql:field="MatchType"/>
<xsd:element name="School" sql:relation="SchoolStudent"
sql:key-fields="DistrictStudent_AutoId"sql:relationship="DSSS">
<xsd:complexType>
<xsd:sequence>

<xsd:element
name="DataVersionId"type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="SchoolOrganizationId"type="xsd:int"sql:field="SchoolOrganizationId" />
<xsd:element
name="IsPrimarySchool"type="xsd:int"sql:field="IsPrimarySchool" />
<xsd:element
name="DateEnrolledInSchool"type="xsd:date"sql:field="DateEnrolledInSchool" />
<xsd:element
name="DateExitedSchool"type="xsd:date"sql:field="DateExitedSchool" />
<xsd:element
name="EnrollmentStatusTypeId"type="xsd:int"sql:field="EnrollmentStatusTypeId" />
<xsd:element
name="NumDaysAttended"type="xsd:decimal"sql:field="NumDaysAttended" />
<xsd:element
name="NumDaysEnrolled"type="xsd:decimal"sql:field="NumDaysEnrolled" />
<xsd:element
name="NumUnexcusedAbsences"type="xsd:int"sql:field="NumUnexcusedAbsences" />
<xsd:element
name="IsSchoolChoice"type="xsd:int"sql:field="IsSchoolChoice" />
<xsd:element
name="IsLAPReading"type="xsd:int"sql:field="IsLAPReading" />
<xsd:element
name="IsLAPMath"type="xsd:int"sql:field="IsLAPMath" />
<xsd:element
name="IsTASReading"type="xsd:int"sql:field="IsTASReading" />
<xsd:element
name="IsTASMath"type="xsd:int"sql:field="IsTASMath" />
<xsd:element
name="IsTitleIMigrant"type="xsd:int"sql:field="IsTitleIMigrant" />
<xsd:element
name="IsSection504"type="xsd:int"sql:field="IsSection504" />
<xsd:element
name="Is21stCentury"type="xsd:int"sql:field="Is21stCentury" />
<xsd:element
name="IsSupplementalServices"type="xsd:int"sql:field="IsSupplementalServices" />
<xsd:element name="IsGifted"type="xsd:int"sql:field="IsGifted" />
<xsd:element
name="IsBilingualProgram"type="xsd:int"sql:field="IsBilingualProgram" />
<xsd:element
name="IsSpecialEd"type="xsd:int"sql:field="IsSpecialEd" />
<xsd:element
name="SpecialEdLREPartATypeId"type="xsd:int"sql:field="SpecialEdLREPartATypeId" />
<xsd:element
name="SpecialEdLREPartBTypeId"type="xsd:int"sql:field="SpecialEdLREPartBTypeId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
<xsd:element name="APCourses" sql:relation="SchoolStudentAPCourse"
sql:relationship="SchoolStudentAPCourse">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="APCourse"type="xsd:int"sql:field="APCourseId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here is the Input File XML
<?xml version="1.0" encoding="utf-8" ?>
<SSID>
<Student>
<DataVersionId>5</DataVersionId>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SSID>123456789</SSID>
<SSN>123456789</SSN>
<FirstName>Cat</FirstName>
<MiddleName>Peka</MiddleName>
<LastName>Boo</LastName>
<BirthDate>12/31/1986</BirthDate>
<GenderTypeCode>f</GenderTypeCode>
<RaceTypeId>1</RaceTypeId>
<DisabililtyTypeId>1</DisabililtyTypeId>
<PrimaryLanguageId>2</PrimaryLanguageId>
<LanguageSpokenAtHomeId>1</LanguageSpokenAtHomeId>
<ZipCode>98123</ZipCode>
<ZipCodeExt>1234</ZipCodeExt>
<ExpectedGradYear>2006</ExpectedGradYear>
<ResidentSchoolDistrictOrganizationId>29103</ResidentSchoolDistrictOrganizationId>
<PreSchool>1</PreSchool>
<IsHomeless>1</IsHomeless>
<IsForeignExchange>1</IsForeignExchange>
<IsHomeBasedStudentAttendingPartTime>1</IsHomeBasedStudentAttendingPartTime>
<IsApprovedPrivateSchoolStudentAttendingPartTime>1 </IsApprovedPrivateSchoolStudentAttendingPartTime>
<DateEnrolledInDistrict>01/01/2002</DateEnrolledInDistrict>
<DateExitedDistrict />
<ELLEnrollmentDate />
<ELLExitDate />
<GradeLevelId>15</GradeLevelId>
<GPA>3.4</GPA>
<FreeReducedMealStatus>1</FreeReducedMealStatus>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/27/2004</LastUpdated>
<MatchType>S</MatchType>
<School>
<DataVersionId>5</DataVersionId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<SchoolOrganizationId>14</SchoolOrganizationId>
<IsPrimarySchool>1</IsPrimarySchool>
<DateEnrolledInSchool>07/22/2002</DateEnrolledInSchool>
<DateExitedSchool>09/25/2002</DateExitedSchool>
<EnrollmentStatusTypeId>4</EnrollmentStatusTypeId>
<NumDaysAttended>25.0</NumDaysAttended>
<NumDaysEnrolled>21.1</NumDaysEnrolled>
<NumUnexcusedAbsences>5</NumUnexcusedAbsences>
<IsSchoolChoice>2</IsSchoolChoice>
<IsLAPReading>3</IsLAPReading>
<IsLAPMath>4</IsLAPMath>
<IsTASReading>5</IsTASReading>
<IsTASMath>6</IsTASMath>
<IsTitleIMigrant>7</IsTitleIMigrant>
<IsSection504>8</IsSection504>
<Is21stCentury>9</Is21stCentury>
<IsSupplementalServices>0</IsSupplementalServices>
<IsGifted>1</IsGifted>
<IsBilingualProgram>2</IsBilingualProgram>
<IsSpecialEd>3</IsSpecialEd>
<SpecialEdLREPartATypeId>1</SpecialEdLREPartATypeId>
<SpecialEdLREPartBTypeId>3</SpecialEdLREPartBTypeId>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
<APCourses>
<APCourse>1</APCourse>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
</APCourses>
</School>
</Student>
</SSID>

Refer to webform fields in Insert statement

Can someone help with this? Let me know if what I'm trying to do is possible...

Here's code example:

@.ClientID int,

@.QuoteID int,

@.Base real,

@.One real,

@.DwellingLimit real

BEGIN

Insert Into tblOne(ClientID,QuoteID,GuideID,GuideRate,GuideMult,Premium)

Select @.ClientID, @.QuoteID, GuideID,GuideRate, @.+"GuideMult"+,GuideRate*GuideMult

From tblTwo

Where Choose = 'True';

END

I need the value stored in tblTwo.GuideMult (ie. One, BaseRate) to be translated

into the numerical value shown on a webform (ie. @.One, @.BaseRate) and then

insert the numerical values into tblOne.GuideMult

Clear as mud? Does somebody have a better way to do this?

You're right -not very clear.

Please post the table DDL, and some sample data in the form of INSERT statements (see this link), and a step by stop expanation of your starting and ending results.

Wednesday, March 7, 2012

Re-Execution of For each Loop Container

I have a For each loop container and inside the same a group of text files are loading to a table using bulk insert task.If there are duplicate files i dont wish to load it into the table. Duplicate check can be done by script task but how do i skip the files and repeat the for loop for the next files.

That means i want to skip some files and load the other files in a for loop.
How do i do that.

Can anybody please help me.

So here is what you do

Loop container has inside a Script task that checks for a duplicate file

and a BULK INSERT task that does what it says on the tin.

The script task and the BI task are joined by workflow.

Declare a variable say something like FileAlreadyThere of type DT_BOOL

(boolean)

In the script task if the file is already there set the value of this

variable to TRUE and if not then FALSE.

The workflow now between the two tasks should be based on an expression

as well as outcome so have a look at this article and you want to set

your expression to something like

@.FileAlreadyThere == FALSE

http://wiki.sqlis.com/default.aspx/SQLISWiki/LogicalOrExample.html

Allan

"Raj Amb@.discussions.microsoft.com"

wrote in message

news:1b962d72-8a6d-4b3c-bdab-e86bfda3270b@.discussions.microsoft.com:

> I have a For each loop container and inside the same a group of text

> files are loading to a table using bulk insert task.If there are

> duplicate files i dont wish to load it into the table. Duplicate check

> can be done by script task but how do i skip the files and repeat the

> for loop for the next files.

> That means i want to skip some files and load the other files in a for

> loop.

> How do i do that.

> Can anybody please help me.|||Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.

but i want to continue/iterate the for loop till the last file , how do i do that.|||

Raj Amb wrote:
Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.
but i want to continue/iterate the for loop till the last file , how do i do that.

This is controlled by two properties:
1) FailPackageOnFailure on each task or container - set it to false to ignore errors.
2) Also, each task and container have MaxErrorCount - increase it to desired value.

Saturday, February 25, 2012

Reducing filesize while deleting rows?

I've got a very filesize restricted database. I noticed that when I insert 1000 rows my filesize jumps to 80k, but when I delete all but 50 of those rows...the filesize actually increases to 84k. How do I make sure the filesize of my database shrinks when I delete rows?

Thanks!

Use SqlCeEngine.Compact() or SqlCeEngine.Shrink() to do that. You can find description on MSDN.

|||Unfortunately I'm using native C++ for all this, so I don't have access to SqlCeEngine. Is there a SQL command i could execute to perform the equivalent to a shrink routine? If that doesn't exist I'll need some way to do it while an existing OLEDB connection is open and operating. I need this database to run 24/7 with no downtime whatsoever.

Thanks for the help!
|||

The database connection must be closed to do shrink or repair. This is a design requirement so that database file structure can be recreated.

What you can probably do is to make a copy of the database , compact it, momentarily drop connection to original database and replace original with compacted database. You will have to take a read only lock on original db while the copy is compacted so that no new changes take place.

|||

The OLEDB provider has an Engine object, which has a CompactDatabase method.

Friday, March 30, 2012

Regarding Data Transpose

Regarding BulkInsert

Regarding BulkInsert

Regarding BulkInsert

Regarding Bulk Insert

Regarding BULK insert

Regarding Bulk Insert

Regarding BULK insert

Regarding Bulk Insert

Wednesday, March 28, 2012

Reg:Tables data changes auditing

Reg: Table data changes auditing

Monday, March 26, 2012

reg Bulk Insert

Tuesday, March 20, 2012

Referencing destination table in INSERT SELECT statement

Referencing destination table in INSERT SELECT statement

Referencing destination table in INSERT SELECT statement

Friday, March 9, 2012

Reference ID problem for import to SQL

Refer to webform fields in Insert statement

Wednesday, March 7, 2012

Re-Execution of For each Loop Container

Saturday, February 25, 2012

Reducing filesize while deleting rows?

reduce databse size

Blog Archive

About Me