Data Extract then create new table

Please help in SQL2000

I have a table called dbo.DataFile which has 31 fields...I need to extract data from this table then create a new table under two conditions:

1. I only need to extract the data if the data from DF_SC_Case_Nbr field doesn't start with '0000%'

2. I need to merge [DF_SC_Case_Nbr] & [DF_SC_Def_Nbr] then call it DF_Combo_SC_Nbr (and keep the rest of the columns in tact), so the new table will now have 32 columns

Can somebody please help with the codes?..thanks. :confused:I don't speak SQL2000 :rolleyes: , but the general idea would be something like this:

CREATE TABLE new_table AS
SELECT column1, column2, ... column 31,
DF_SC_Case_Nbr || DF_SC_Def_Nbr DF_Combo_SC_Nbr
FROM dbo.DataFile
WHERE DF_SC_Case_Nbr NOT LIKE '0000%';


Merging two columns can be done using concatenation operator (in Oracle SQL represented by "||").|||Yeah that sounds right...but is there anybody out there who can translate this so sql can understand it?... :confused:|||Yes, but this sounds like homework. If you understand the basic idea and at least something about SQL Server 2000, then I think you should be able to type the answer as fast as I can.

If you are missing something, please explain. If you are trying to get someone to do your homework, sorry, that isn't my style.

-PatP|||Hi All,
I am using the sqlldr to load data with direct=true and parellel=true.
now my question is i have some column with unique indexes and primary keys also. if at all any record is duplicate then index becomes unusable. so what is the solution to delete those records which are violating unique indexes.

2) can any one provide me the query which making use of hints which does not make use of the index which is in un usable state and delete the records.|||You could create a temporary table which looks exactly like your original table, but without any indexes / unique / primary keys.
Load data into the temporary table.
Insert data into original table, eliminating duplicate rows.

I'd say you'll finish it much faster that way than trying to make Loader do a job itself.sql

Data Extract Question

Hi, I'm hoping someone has an idea or two on this topic. Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
----
ContactID
Email
Name
tQuestion
----
QuestionID
Question
tAnswer
----
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to seethe data with one line per contact, but showing every answer to everyquestion... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc......
Obviously to get the data I cansimply do an outerjoin to get allcontact data then all questions, and answers that exist... but thatwill obviously return tabular data with one row per eachanswer... Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively... any help would be more thanappreciated. Thanks in advance.
-e

emaxwell wrote:

Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively

Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).

Data Extract - Partitioning

Hello,

I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.

I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?

Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3

Please help. Just can't figure this out!

Meera

Meera:

Exactly what kind of error(s) are you experiencing? It appears to me that you have spurious "quotes" in your query.

Dave

|||

Meera:

I mocked the data with this table:

create table [Departmental Risks]
( DepartmentName varchar(30),
[Risk Scenario] varchar(30),
cost numeric (9,2)
)
go
insert into [Departmental Risks] values ('Dept A', 'Scene 1', 45.32)
insert into [Departmental Risks] values ('Dept A', 'Scene 2', 29.95)
insert into [Departmental Risks] values ('Dept A', 'Scene 3', 71.45)
insert into [Departmental Risks] values ('Dept A', 'Scene 4', 54.34)
insert into [Departmental Risks] values ('Dept B', 'Scene A', 21.45)

I then ran tried this query and obtained the result that follows:

select DepartmentName,
[Risk Scenario],
cost
from ( select DepartmentName,
row_number () over
( partition by DepartmentName
order by cost desc, [Risk Scenario]
) as Seq,
[Risk Scenario],
cost
from [Departmental Risks]
) a
where seq <= 3
order by DepartmentName,
cost desc,
[Risk Scenario]

-- -- Output: --

-- DepartmentName Risk Scenario cost
-- -
-- Dept A Scene 3 71.45
-- Dept A Scene 4 54.34
-- Dept A Scene 1 45.32
-- Dept B Scene A 21.45

See if this is in the direction you are aiming.

Dave

|||

Hi Dave,

I tried that in both MSQuery and MsAccess. In MS query the message i get say could not add the table '('. In Access I get a syntax error in query expresion 'row_number()...

Am at a loss!

Here is whatI used:

select [RA07 - Departmental Risks].DepartmentName,
[RA07 - Departmental Risks].[Risk Scenario],
[RA07 - Departmental Risks].annualriskcost
from ( select [RA07 - Departmental Risks].DepartmentName,
row_number () over
( partition by [RA07 - Departmental Risks].DepartmentName
order by [RA07 - Departmental Risks].annualriskcost desc, [RA07 - Departmental Risks].[Risk Scenario]
) as Seq,
[RA07 - Departmental Risks].[Risk Scenario],
[RA07 - Departmental Risks].annualriskcost
from [RA07 - Departmental Risks]
) a
where seq <= 3
order by [RA07 - Departmental Risks].DepartmentName,
[RA07 - Departmental Risks].annualriskcost desc,
[RA07 - Departmental Risks].[Risk Scenario];

|||

Is your target database an Access database or a SQL Server 2000 database? The errors you are getting indicates that the target database is not SQL Server 2005.

Dave

|||

Dave,

Its an Access Database

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Daily file processing help

Hello DBA's:

I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load.
Here is the approach and possible implementation.

Approach
I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.

Here is the implementation given by a member of dbforums -

You'll then have to deal with 3 potential actions,

INSERT: New records on the file.
DELETES: Records that don't exists.
UPDATES: Records that are on the file, but attributes have changed.

You had given me this code template.

CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition)
DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
----

Can anybody look at My comments and answer them or revise this code template if need be?

Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.

ThanksVivek,

Did you test it? Run it I mean?

I worked with a guy named Vivek once...

First the DELETE's. You know why you want to delete first, correct?
If yes, and you want to retain history, create an identical historical table called:

CREATE TABLE myTable99_H (
HIST_ADD_DT datetime DEFAULT GetDate()
, Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)

With no constraints..it is history after all, and is inheriting the constarints from the base table

As far as the logic...if you look it's a LEFT OUTER JOIN between the 2 tables WHERE the key DOESN'T exist in the other (NOT NULL)

Got it?

Second, INSERTS...

Same logic as to why (NOT NULL)

Well did you cut and paste the code in to QA?

It should run for you no problems and should be a good example...|||Brett,

Yes it works. Sorry about that friend. I should have tested it in QA before. Thanks. Just a one more thing - I want to do the daily processing only on those records that have been updated/inserted. My daily processing involves checking if those attribute columns Col6 and Col 7 are present in this other file. If they are present than I need to say myTable99 records is Yes - it is present.

So I don't want to check on the attributes that have not changed. Would you suggest using a flag/status field for this purpose? Is there any other way?

Thanks

Vivek

1. Instead of deleting the row first, I have to move that row to a history table and then delete it. So is this correct for Delete -

CREATE TABLE myTable99_H (
HIST_ADD_DT datetime DEFAULT GetDate()
, Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)

INSERT INTO myTable99_H(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

Then do the delete

DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

Originally posted by Brett Kaiser
Vivek,

Did you test it? Run it I mean?

I worked with a guy named Vivek once...

First the DELETE's. You know why you want to delete first, correct?
If yes, and you want to retain history, create an identical historical table called:

CREATE TABLE myTable99_H (
HIST_ADD_DT datetime DEFAULT GetDate()
, Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)

With no constraints..it is history after all, and is inheriting the constarints from the base table

As far as the logic...if you look it's a LEFT OUTER JOIN between the 2 tables WHERE the key DOESN'T exist in the other (NOT NULL)

Got it?

Second, INSERTS...

Same logic as to why (NOT NULL)

Well did you cut and paste the code in to QA?

It should run for you no problems and should be a good example...|||I don't worry about it...it's like picking up sand with tweazers...

As long as you keep history of deletes AND updates with triggers, you will always have a total view of the "life" of the data as it "grows up" (and potentially dies - DELETE).

You can then determine with SQL what happened when and where (and if you add the right columns, by whom).

BUT...

You van look up the COLUMNS_UPDATED syntax in the Trigger

I don't use it, so sorry...

Go to BOL and look up CREATE TRIGGER in the index...

BUT...I'm perfectly happy with my method of tracking and retaining history...|||Brett,

I agree that your method is good. I would do the same.

My daily processing involves checking if those attribute columns Col6 and Col 7 are present in this other file. If they are present than I need to say myTable99 records is Yes - it is present.

So I don't want to check on the attributes that have not changed. That is the records with NO CHANGE shouldn't undergo any processing. Only those that have been updated/inserted should be processed.

Would you suggest using a flag/status field for this purpose? Is there any other way?

Thanks

Vivek|||OK, So you're saying, any rows that are identical you want to bypass?|||Yes. Any rows that are identical should be bypassed. I have already done the processing on these rows. So I don't want to do it again. Only updates/inserts should be processed.|||And DELETES...

But if Col1 and 2 or Key

and in the update I say col3 <> col3
Or col4 <> col4

isn't the extension of the col6 <> col6
or col7 <> col7

You're losing me...

Because that would do what you're asking...|||Brett,

Let me make it simple...

CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','d','e','f' UNION ALL --UPDATED
SELECT '1','3','g','h','i' UNION ALL -- --NO CHANGE
SELECT '1','4','j','k','l'
--DELETED

--This was my initial load...I check if Col4 or Col5 or Col6 (Name fields) are present in a Name file containing a list of names. If they are then I output the record plus the Name record from the Name file into a different table. That is my processing. I have a stored proc that does this. So now during the day this file gets updated in the source system. So the next day what I have is -

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO

Select * FROM myTable00.

I compare this with myTable99, do the deletes,updates and inserts like you said to get the appropriate contents of Table00 to Table99. Are you with me till this point. So now my Table99 next day looks like -

1','1','a','b','c' --NO CHANGE (So today I don't have to check this record with the Name file.)
'1','2','x','y','z' -- UPDATE ( I have to check this record with Name File)
'1','3','g','h','i' --NO CHANGE (No check. If I check and output with Name record then duplicate row)
'2','3','a','b','c' --INSERT (Check)

Have I made it clear now?

Let me know what you think. Appreciate your help.

Vivek|||Originally posted by vivek_vdc

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO


Clear as mud...but I've got way too much time invested...

You can't do that..see the bold...you're pk is col1 and col2...they can'r appear more than once like your sample suggestions...

again, unless it's too murky in here...

Cutting to a certain word

Hi there, i need to know how to cut a string to the nearest word. For example, i've got an article and i need to extract just a part of the beginning, i could use LEFT([content], 250) but there is little chance this will cut on a word. Therefore i need to know if there is a function that will cut to the nearest word in T-SQL or i will simply put a summary field in the database. (I prefer to generate the summary on the fly if possible)

You could use CHARINDEX.

SELECT LEFT(column, CHARINDEX(' ', column) ) FROM yourTable

|||

I just looked up what you said and this doesn't make sense...

I didn't even try but i know what this will result in... Returns the first word of a column...

But i guess if i provide a startAT value(param 3) to charindex i can start the search at 250 characters and it will cut accordingly...

|||

Yes, this will always give you a full word:

Declare @.tvarchar(100), @.startatintSET @.t ='this is a good test and a long sentence to test end of word'SET @.startat = 33SELECT @.t,left(@.t,@.startat + CHARINDEX(' ',right(@.t,len(@.t) - @.startat)))