SQL question.

[Devil]

Hopefully there's an SQL guru out there that can help with this.

Using SQL Server 2000, shifting a couple of tables from one db to another, receiving an error with a couple of the tables about Foreign Key constraints. The error tells me the row in the database and what field is the problem. I'd like to jump into query analyser to find out whats in the fields but what is the syntax to specify a particular row?

I cant just open the whole database, we're talking a few million rows here (literally).

Select * from table where ?????(row number) = 3345227.

Help?

[WRT]

Our SQL guru left earlier on this morning sorry. My experience is limited, but have never tried to address a record by its row number so dont know if it is even possible. Can you get any other info on whats in the row and base your query on that?

If you are still having probs on Monday I'll run it past our SQL consultant and see what he comes up with.

[Devil]

Unfortunately not. Just a rownumber, database, table and fieldname, but no values. Bugger.
Cheers.

[WRT]

Just been trying to see if I can find a way for you, but none of our references seem to outline such a method. All the WHERE clauses I can find are based solely on the data within a given field, rather than something like what you are looking for.

Google groups are coming up blank so far too.

[manuboy]

Sql 200 has no "row" number concept when selecting. You must use a known column value in the where clause.

Got the actual error message? i'd like to see it...

Another option is to disable RI temporarily on the destination, copy the data then use a sql statement(s) to determine all rows that would fail RI like

select [columns] from [table1] where not exists
(select [foreign_key_column] from table2 where table1.[fk_key_column] = table2.[fk_column])

note: stuff in [] is placeholders

How are you shifting them? DTS? Transact Sql via "select into"? via enterprise managers object copy? ... may as well tansfer this to a PM - no use bothering the good people of KB with Sql stuff....

[Devil]

Tis in off topic anyway, its a thread now so may as well make the most of it!
Using DTS (Tables and Data) to shift the info. The error messages are as follows (well, they're pretty much all the same, jsut different tables/fields).

Its actually trying to shift historical session data from an Old Webmarshal database to a new one.

"Error at Destination for Row number 2376219. Errors encountered so far in the this task: 1.
The statement has been terminated.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DomainLog_SessionLog'. The conflict occured in database 'WebMarshal2', table 'SessionLog', column 'SessionLogId'."

Just had an idea, I was shifting the SessionLog table at the same time, but it was processed after the above table. Maybe I need to shift that table first by itself then shift the others? (DomainLog, DomainFileLog, URL).

[Ixion]

..
Using DTS (Tables and Data) to shift the info. The error messages are as follows (well, they're pretty much all the same, jsut different tables/fields).

Its actually trying to shift historical session data from an Old Webmarshal database to a new one.

"Error at Destination for Row number 2376219. Errors encountered so far in the this task: 1.
The statement has been terminated.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DomainLog_SessionLog'. The conflict occured in database 'WebMarshal2', table 'SessionLog', column 'SessionLogId'."
...
Yes, you need to shift the Sessionlog table (the one with the primary key value) first. Otherwise the odds are that the tables are not copied in sync and the DomainLog copy tries to write a record that doesn't yet ahve a Sessionlog key. Or else disable the foreign key while copying and then restore it afterwards.

[ManDownUnder]

A way around it might be to find the offending SessionLogId which is being duplicated (and should be unique by the look of the validation)

Select SessionLogId, count(*)
From SessionLog
Group by SessionLogId having count(*) > 1

From there you could export the rows, delete them from the db and/or re-enter them?

Not sure if that's 100% MS SQL standard but it's what I learned (a few years ago)

Or, once you know the SessionLogId with >1 entry, just select it (Select * from ... where SessionLogId = 'xxx') and look at the rows returned,

Issue a targeted delete statement on the offiending ro (where SessionLogId = 'xxx' AND other_condition = 'unique value')

MDU

[Devil]

Good ideas people. Cheers. Takes a question like this to bring the geeks out of the woodwork muahaha!

[limbimtimwim]

Select * from table where ?????(row number) = 3345227.Does M$SQL have a way of doing

'SELECT * FROM blahTable OFFSET 7 LIMIT 1 ;'

Which would suffice.

[wendigo]

Can only recommend shifting the parent tables first. Otherwise the lack of a valid FK, which the child is reliant on will make your app drop its guts.

"I'd like to jump into query analyser to find out whats in the fields but what is the syntax to specify a particular row?"

Don't know the syntaxt for SQL Server 2000 but you could try something along the lines of:

SELECT * FROM (SELECT * FROM [table] ORDER BY .... ) WHERE ROWNUM = [rownumber];

[manuboy]

Does M$SQL have a way of doing

'SELECT * FROM blahTable OFFSET 7 LIMIT 1 ;'

Which would suffice.

No, it might work in PostGre / MySql but not here in MS land...

[ManDownUnder]

Good ideas people. Cheers. Takes a question like this to bring the geeks out of the woodwork muahaha!

I resent that - I'm in SALES!.. hang on - that's worse...

[Devil]

I resent that - I'm in SALES!.. hang on - that's worse...
Closet geek? :D

[Sniper]

Im hiding in the corner. SQL scares me