I'm using DTSWizard to import a table from my main database to Temp.
This is the SQL statement...
CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)
What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.
Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?
This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.
Thanks,
Bill
Do you have to use the DTSWizard?
If not, BULK INSERT has a FIRSTROW parameter.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Hi Wesley,
I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.
Thanks,
Bill
|||It is indeed a TSQL statement. The Books Online has excellent documentation and examples.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Hi Jens,
In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the
Create Table Query?
Thanks,
Bill
|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Hi Jens, thanks for getting back with me.
You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?
Thanks,
Bill
|||Hi Jens,
What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.
Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.
Thanks,
Bill
|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
Hi Jens, thanks for getting back with me.
I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...
CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[custom1] varchar(255),
[custom2] varchar(255),
[custom3] varchar(255),
[custom4] varchar(255),
[custom5] varchar(255),
[bounces] int
)
I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?
Thanks,
Bill
|||
You can use this as the source of the table (instead of using the table direct)
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM SomeTable
) SubQuery
WHERE ROWCOUNTER>5000
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.
Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)
- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
|||Well just a comma missing
SELECT
*
FROM
(
Code Snippet
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for getting back with me Jens.
I ran your code in DTS Wizard.
This is the error I received...
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Error)
Messages
Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)
- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
No comments:
Post a Comment