Mysterious ATOTransfer.log During Imports && Table Copying

Imports Table Structures In SQL 2000 Into Excel
Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

Agustina

View Replies !   View Related

---

Capturing Mysterious Truncation/deletion Of A Table
I have a dts which creates a table which is utilized on my localintranet. The DTS runs without error and the table iscreated/populated/transfered to the appropriate db. Then it appearsthat there is an action on this table which truncates it. I have beenunable to determine the culprit. Can I create a trigger that willcapture truncation? I have tried to create a trigger to capture thisinformation but none that I attempt seem to work on capturing atruncation or a drop table and re-create.Any help would be greatly appreciated.MT.

View Replies !   View Related

---

HELP,,Imports A Data From Excel File Into A Table In SQLserver Database
Dear all,,
I need your help,,I'm work in website project using ASP.NET,,I have to register the users of this site,, the users are over 200,,so,,I'm thinking in away to save my time,,All the information of these users are stored in Excel file,,What I want to do is to imports these data from the excel file into a table in my database(SQLserver database),,Could you help in coding by VB.NETThanks in advance,,

View Replies !   View Related

---

Copying All Rows From One Table Into Another Existing Table And Overwriting Data
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanks

View Replies !   View Related

---

Data Transformation: Copying A Table To A Table... How Does It Work?
Hello,

Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.

In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?

View Replies !   View Related

---

Copying Temp Table Data To Permanent Table
Hello guys..

Can u plz help me by giving me an idea how i can copy the temp table data to permanent table

Thanks,
sohails

View Replies !   View Related

---

Copying Specific Data From Table In DB1 To Table In DB2
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp

View Replies !   View Related

---

Update A Table By Copying A Column From Another Table
I need to update a table by copying a column from another table(having the samestructure, but on another database), from the record having the sameprimary key.1 - What is the correct query?2 - I tried copying them record by record, but the datatype is ntext,(it displays <longtext> in the result pane), and trying to update it results in thefollowing errormessage:The text, ntext, and image data types are invalid in this subquery oraggregateexpression.I tried variations of the following:UPDATE TABLESET column0 = (SELECTcolumn0FROManotherDB.dbo.TABLEWHEREanotherDB.dbo.TABLE.column1 = column1)WHEREanotherDB.dbo.TABLE.column1 = column1

View Replies !   View Related

---

CREATE TABLE FROM ANOTHER TABLE WITHOUT COPYING DATA
select * into dbo.ashutosh from attribute where 1=2

"USE WHERE 1=2 TO AVOID COPYING OF DATA"

//HERE "ASHUTOSH" IS THE NEW TABLE NAME AND "ATTRIBUTE" IS THE TABLE WHOSE REFERENCE IS USED//
//the logic is to use where clause with 1=2 which will never be true and hence it will not return any row//

View Replies !   View Related

---

Copying Data From One Table To Another Table In Another Database
I have "inherited" a project working on a SQL 2000 database.  The project calculates commissions based on data from an invoice header table and an invoice details table.  The goal is to extract data from the primary database tables, perform limited manipulations, and store the resultant data into a table in a different database for reference and reporting.  I have the query complete that extracts and manipulates the data, but I am stuck in trying to add this data to the final storage/reporting table. I would also like to do error checking to be certain that a record is not "re-inserted" from the source data to the destination table. Thanks in advance, Barry

View Replies !   View Related

---

About Copying Table..
just wondering how can i script out so that i can copy a table from a database but not the data itself..
.

View Replies !   View Related

---

Copying A Table
I am assuming there is some functionality to copy and paste a table in sql server..however I am not seeing where I can do this what I want to do is this:

create table1 as select * from table 2;



Please let me know how I can create a new table as a replica of an existing table. Thanks so much!

View Replies !   View Related

---

Copying A Row From One Table To Another
Hi, I'm pretty new to SQL so am still learning the syntax etc.

I have 2 tables, one called Railways and another called Sheet1$ (an import from Excel).

In Railways, I have 5 Fields:

ID
First_Name
Last_Name
File
Full_Name

At the moment, all are populated with data (300 records) but Full_Name is showing values of <NULL>.

I want to populate Full_Name with the Data in Sheet1$ which has the field Full_Name (which is populated with the data I need)

What is the query I need to run to get SQL to copy the data from Sheet1$ (Full_Name) into Railways (Full_Name) - replacing all of the <NULL> values?

I've tried using the import wizard but without success.

Thanks in advance.

View Replies !   View Related

---

Copying A Table From One Db To Another
I have 2 databases on the same sql server. Both have the same tables. I need to copy the contents of tableA from the source database to TableA on the destination database. Can I do this using a SQL script or some kind of script, and if so how and what would the syntax be.

please reply via e-mail also...res2100@yahoo.com

View Replies !   View Related

---

Copying A Table
Hi,o

I found an option to copy a table without having to script the table, now I can't find it.  Is there an option to do this?  When I selected the option, it didn't work.  Any ideas?  Also how do you turn off trace and statistics?

thx,

Kat

 

View Replies !   View Related

---

Copying A Table From One Database To Another?
Hi,Whats the best and easiest way to copy a table from one database to another via ASP.net?I want to copy from SQL Server to Oracle.  I have an ODBC connection for Oracle.I was thinking I could use a DataSet, but wasn't sure of the details on doing this.  Am I off base on this?What I have so far..1) I can get the data from SQL Server into a DataSet.2) I can establish a connection to Oracle via ODBC and the database has the appropriate table structure.How do I 'use' the SQL Server DataSet to copy the data to Oracle?Thanks,Scott 

View Replies !   View Related

---

Copying A Table From One Database To Another
Hey
in query analyzer, how do you copy a table form one db to another db
i thort it was something like
select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld
cheers

View Replies !   View Related

---

Problem Copying Table
I am trying to copy a table to another SQL 2000 Database, but Icontinually get errors.When I right-click, choose All-Tasks, Export-Data and go through theDTS settings, I get an 'Unspecified Error' on row 107 (which, lookingat all visible data, there appears to be no difference than the 100rows before it).Failed: Copying to a different database.Failed: Copying to the Same database with a different table name.Succeeded: Exporting to a Text file (not helpful however)Different tables copy just fine.The design is a primary key (integer, auto incrementing), 10 textfields and another integer field.Has anyone ever run into this silly/unhelpful error message before?

View Replies !   View Related

---

Copying A Record From One Table To Another
What is the easiest way to copy a record from one table to another (withidentical structure)?Regards

View Replies !   View Related

---

Problem In Copying A Table Into Another One
I am having problems to copy a table into another one

SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)

with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10

now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10

what can be the problem ? where does the 9 comes from

thank you

View Replies !   View Related

---

Problem Copying Table
We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.

View Replies !   View Related

---

Copying A Record Within The Same Table...
Hi. I would like to copy some records within the same table. what happens is we get very similar new cases in our office and we want to apply the same information from one client to the new client.

i looked at:
INSERT INTO ClientSpecs (ClientID, Spec1, Spec2, etc.)
SELECT Spec1, Spec2 from ClientSpecs WHERE ClientID=1234
but i want to use the new client id in place of the old client id. is there a way to do this? does this make sense? we're basically copying one client's file to a new client's file, except we need to use the new client's id.

View Replies !   View Related

---

Copying Over Differential Of SQL Table
Hi,

I have a DB on a SQL server version SQL 2000. I have copied over one table onto a different server into a newly created DB. This DB is on SQL 2005.

The tables are identical in structure.

Now I need to copy over the differential from the Source table on the SQL 2000 server into the table on the destination server which is SQL 2005 on a daily basis.

What is the best way of doing this? It is just 1 table, I do not need the entire DB.

This table does hold a lot of records and on a daily basis there will be roughly 20,000 that need to be copied over. One of the main reasons for the differential only is that these 2 servers are physically located on different continents so the connection speed is not that great.

The table in the Source holds about 36 million records already (which I copied over to get the initial start).

Any ideas, help, suggestions are greatly appreciated.
Thanks,
Michiel

View Replies !   View Related

---

Query For Copying Table
Hi,
I wanted to know a query in sql that would copy a whole table from one database to another database with both the structure and the content.
strangely, my college professor don't know the same.
I tried looking in the help menu in sql but it didn't helping me much.
Help me out...!! I have a deadline to meet. I really would appreciate it. thanxs. --vijay.

View Replies !   View Related

---

Copying SQL Server Table...
I`ve create a table which has 10 fields...Now I have to create 12 more tables which has same fields...
How can i duplicate or copy a table ?

View Replies !   View Related

---

Copying Identical Table
How can I create a table identical to another one?
I need to copy the indexes a constraint too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

Thank you

View Replies !   View Related

---

Problem In Copying Table
When I copy a table to another SQL server database, the IDENTITY and other constraints don't get copied. Is there any way to do it apart from running ALTER script separately. And, how do I Add IDENTITY(1,1) to an already existing table column.

View Replies !   View Related

---

Copying A Column Within The Same Table
I need to copy a column which contains a list of integers to a new column. Can someone please tell me how I can do this using a query statement? Thanks in advance.

View Replies !   View Related

---

Copying Table Rows
Hello,

I need to perform a query on a table. With the results of the query, I want to copy those results into the same table that I ran the query on. The trick is, I need to update 3 of the values before I can re-insert the results back into the same table.

Does anyone know how I can do this?

Thank you,
Crystal

View Replies !   View Related

---

DTS - Copying Table Data
hi all,

i have a dts that copies table contents from remore server to local server.
remote table:
ItemID|name
1 |name1
2 |name22
3 |name33

local table:
ItemID|name
1 |name1
2 |name2

i want that the dts will only copy the new rows( aka. row with ItemID = 3) and leave the other rows a they are.

can any one help me create such a dts.



please help!

View Replies !   View Related

---

Copying Row Data Within The Same Table
I have the following table:

Table name: RR    

columns:

Subject (varchar (35), Null)

Topic  (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic.  However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'.  I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'.  Highest current RRID = 5008 

I cannot figure out how to write that query.  I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

View Replies !   View Related

---

Copying Table From One Database To Another
How can you copy a table from one database to another database?  I used to be able to do it with SQL Server 2000 but I can't figure out how to do it with SQL Server Management Studio Express for SQL Server 2005.   Do I need other software?
Dave

View Replies !   View Related

---

Copying Columns From One Table To Other
Hello to all,
this is my first post on this forum, and I hope you'll be able to solve my problem. I'm working in industrial field and only from time to time I have to work with SQL. My problem is the following:
I have two tables. Both tables have same number of rows but different number of columns. Table 1 has 5, and table2 has three columns.
I need SQL statement to copy two columns A and B from table 1 to table 2 with condition, for example table2.C <> 2?

I don't know if I was clear enough. Basically I need to copy data from two columns of one table to other. Tables have no common data which I can use to join tables.
I tried something like this:

update table2 set (A,B)=(select A,B from table 1) where table2.c <>2
but don't know how to use syntax properly...

Thanks in advance

View Replies !   View Related

---

Copying One Table To Another Database
How can I copy a table from one db to another? Is there copy paste method.

View Replies !   View Related

---

Copying Fields In The Same Table
I'm kind of new to SQL and would appreciate some help with this. I have a table named Company. In this table there are fields called CompanyID, CompanyName, Address, and Year. (There are other fields too, but they are not relevant for this post.) There are multiple companies in the table and each company has a unique ID number. However, each company can have up to three records in the table. (One for the year 2005, one for the year 2006, and one for the year 2007.) Currently, only a company's 2007 record has data for the address. It's 2005 and 2006 records have addresses that are blank. I want to be able to update the 2005 and 2006 records with address from the 2007 record. Obviously, a very simple update won't work because there are multiple companies and multiple records that need updating. Can anyone fill me in on the SQL statement(s) I would need to make this happen? I would be extremely appreciative.

View Replies !   View Related

---

Copying Table And Data
Hi All

Iam using sql 2005 and am new to it, I want to copy a table and data from server to another, how is this possible?

Many Thanks in advance....

View Replies !   View Related

---

Copying Records In Same Table
Before I start asking for favours I think it only polite that I introduce myself. My name is Doug I'm a EC&I engineer from the UK.

I'm developing a recipe managment system with SQL Server 2005 at the database system. I have a table which contains recipe parameters for an industrial process. I would like to generate a stored procedure which will copy either all or just some of the fields from one record in a recipe table to another record in the same table while retaining the unique recipe id. For example,
CREATE PROCEDURE sp_CopyRecipes
@RecipeNumberCopyFrom int
@RecipeNumberCopyTo int
AS
SELECT Param1,Param2,Param3 FROM tblRecipe
WHERE
RecipeID = @RecipeNumberCopyFrom
(And Copy to)
SELECT Param1,Param2,Param3 FROM tblRecipe
WHERE
RecipeID = @RecipeNumberCopyTo

there are 45 recipe paramters. There is a requirement to copy all paramters or just a selection. I written a procedure in the VB.NET front end which works well, but I'd prefer it to be done on the DB server. Thanks in advance
Hope I can contribute in the future

View Replies !   View Related

---

Copying Column From Other Table .
There are several easy ways to accomplish what I am trying to do in a simpler way however (long story short) this is something I need to learn for a much larger task in the near future.
O.K. I have a table named clients I'm trying to get started copying columns from the same table one by one. Lets say original table has these columns:
Database kokomain -- table dbo.clients
ID(pk) fname lname idate

I've created a blank temp table with the exact same schema and named it 'temp'. I've populated the id,fname and lname fields with the statement:
Insert Into kokomain.dbo.temp (cid,fname,lname) select c.cid,c.fname,c.lname from kokomain.dbo.clients c

Now, how could I add the idate field to the table as a separate query? I've tried several variations of things like...
INSERT INTO kokomain.dbo.temp (idate) select c.idate from kokomain.dbo.clients c where c.idate in (select idate from kokomain.dbo.temp)
No dice.

The correct statement would be greatly appreciated
Thanks.

View Replies !   View Related

---

Copying A Table From One Database To Another
how can I copy a table and its contents(data) from one database to another database?

View Replies !   View Related

---

Copying Data From One Table To Another Table
I need to copy data from one table into another table. The field names are NOT the same:

I need to INSERT

Table 1 fields:
cdescurl
cname
cdescription
cimageurl
category
cprice

INTO

Table 2:
product_url
name
description
image_url
category
price

How do I do this? Thank you in advance for any help you can give me!

View Replies !   View Related

---

VisualStudio 2005 - Table Copying?
I am using VisualStudio 2005 and have Access 2007.  I have two databases located on two different remote hosted Sql 2000 servers.  Is there any way to copy a table from one of these databases to the other database, both data and structure?
Thank you

View Replies !   View Related

---

Copying Table Structure Using VS 2005
I is possible to copy the structure of an existing SQL 2000 table to make a new table with Visual Studio 2005?

View Replies !   View Related

---

DTS-Copying Data Into Multiple Table
Hi,
I am trying to copy data from one table on SQL Database to multiple database in access database using DTS(Data transfomration services). I am able
to select data from multiple table in SQL database but i am not given a choce to dump it into multiple table. I would appreciate any help.
Email me at jessinus@hotmail.com
Thanks
Jesal

View Replies !   View Related

---

Copying Table In Stored Procedure
How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

Thank you

View Replies !   View Related

---

Copying Table In Stored Procedure
How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

I can't script the table because this process must be automatic no manual.

Thank you

View Replies !   View Related

---

Copying Datas From View To Table In DTS
Hi Everybody,

Can anybody tell me how to transfer records from the "View" Object in 6.5 to a "Table" in 7.0 through DTS. I want to schedule this Job so that whenever I execute this job it should truncate the table in 7.0 and append the records from the View in 6.5

thanks,
Sri

View Replies !   View Related

---

Copying Image From Database Table
Hi,

I work for a non-profit organization that uses software to keep track of it's clients. This software has the ability to store photos with the clients file. The photo gets copied into a database table in the Microsoft SQL Database. We need to get at that file and I came across a command that does this. It's called textcopy. I am not sure what I'm doing wrong, but I keep getting error messages when I run this command. Here's what I type in:


Code:

C:>"C:Program FilesMicrosoft SQL ServerMSSQLBinn extcopy.exe" /S SERVERNAME /U USERNAME /P PASSWORD /O /D DATABASENAME /T dbo.PhotosMen /C PhotoMen /W "where PhotoMen = 1" /F C:image.jpg
TEXTCOPY Version 1.0 DB-Library version 8.00.194SQL Server 'ACRMDELL' Message 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (Concerning line 1)
DB-Library Error 10007: General SQL Server error: Check messages from the SQL Server.ERROR: Query execution failed.



Any help would this would be appreciated.

Josh

View Replies !   View Related

---

Copying Columns To Table With Different Design
I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design.  I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data.  Here are the two tables involved in the column copy:
 
Documents Table
DocID (Primary Key)
DocNo
SequenceNo
YearGiven
MonthGiven
DayGiven
 
FullDocuments Table
          FullDocID (Primary Key)
          DocNo
          SequenceNo
          SectionText
          YearGiven (empty €“ no data)
          MonthGiven (empty €“ no data)
          DayGiven (empty €“ no data)
 
After the copy column procedure I want the FullDocuments table structure to look like this:
 
FullDocuments Table
          FullDocID (Primary Key)
          DocNo
          SequenceNo
          SectionText
YearGiven (full of transferred data)
MonthGiven (full of transferred data)
DayGiven (full of transferred data)
 
The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table.  This is because each document has four types €“ the primary Text document and three supplemental documents (Background, Report, and Index).   Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types).  Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns.  Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).
 
I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables).  This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization. 
 
I tried exporting the data using the Export Wizard using the €œCopy data from one or more tables€? option.  The transfer failed.  The most relevant lines of the Error Report are:
 
 
·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
 (SQL Server Import and Export Wizard)
·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
 (SQL Server Import and Export Wizard)
 
·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
 (SQL Server Import and Export Wizard)
I then tried the €œWrite a query to specify the data to transfer€? option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck.  Questions:
 
1.  Is the query approach the best way to achieve my objective, or did I miss something in the €œCopy data€? option in the Export Wizard?
 
2.  If the query approach is the best way, any suggestions on what the query will look like?
 
Thanks for any help you can provide.

View Replies !   View Related

---

Data Not Copying To MS Access Table
Hello,
I have a Data Flow Source that uses a SQL Command to pull data.  In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.
 
I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults. 
 
I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.
 
How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2
 
DECLARE @CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)
--and fill it
INSERT INTO @CategoryTable
(ColID, ColCategory, ColValue)
SELECT
 0,
 LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
 LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int
SET @ID = 1
UPDATE @CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END, 
@ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
SELECT --cast to Nvarchar for MSAccess
 a.ColID,
 CAST(a.ColValue as Nvarchar(30)) AS OrderID,
 COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
 COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
 COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
 COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
 COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
 COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
 CASE
  WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
  ELSE CAST(h.ColValue AS SmallDateTime)
 END AS DisbursementDate,
 --COALESCE(h.ColValue,'') AS DisbursementDate,
 CASE
  WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
  WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
  WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
 END AS ReturnDate,
 --COALESCE(i.ColValue,'') AS ReturnDate,
 COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
 COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
 
FROM @CategoryTable a
LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'

View Replies !   View Related

---