Read, modify table (locking) question

I am quite new to MS SQL, and I want to read rows from a todo table, and
when a row has been processed, I want to delete that row.

Ages ago in MySQL I would probably have locked the table, select, process
a row, delete a row, unlock the table.

I have been reading through the documentation from MS SQL, but it's not
clear what exactly I should do.

Since I want to lock only one table in the select (the others just provide
data, and are not modified), what's a good solution?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.htmlJohn Bokma <john@.castleamber.com> wrote:

> I am quite new to MS SQL, and I want to read rows from a todo table,
> and when a row has been processed, I want to delete that row.
> Ages ago in MySQL I would probably have locked the table, select,
> process a row, delete a row, unlock the table.
> I have been reading through the documentation from MS SQL, but it's
> not clear what exactly I should do.
> Since I want to lock only one table in the select (the others just
> provide data, and are not modified), what's a good solution?

what I came up with:

BEGIN TRANSACTION
SELECT TOP 10 .... FROM A WITH(ROWLOCK,HOLDLOCK), B, C WHERE ...
....
... delete each row in A in TOP 10
END TRANSACTION

what I want to prevent is that more then one process selects 10 rows, and
starts to delete rows (from A) that are selected by any of the other ones.

to me, a rowlock is sufficient, and fine grained enough, and the hold lock
holds it to the end of transaction.

Am I right?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html|||John Bokma (john@.castleamber.com) writes:
> John Bokma <john@.castleamber.com> wrote:
>> I am quite new to MS SQL, and I want to read rows from a todo table,
>> and when a row has been processed, I want to delete that row.
>>
>> Ages ago in MySQL I would probably have locked the table, select,
>> process a row, delete a row, unlock the table.
>>
>> I have been reading through the documentation from MS SQL, but it's
>> not clear what exactly I should do.
>>
>> Since I want to lock only one table in the select (the others just
>> provide data, and are not modified), what's a good solution?
> what I came up with:
> BEGIN TRANSACTION
> SELECT TOP 10 .... FROM A WITH(ROWLOCK,HOLDLOCK), B, C WHERE ...
> ...
> ... delete each row in A in TOP 10
> END TRANSACTION
> what I want to prevent is that more then one process selects 10 rows, and
> starts to delete rows (from A) that are selected by any of the other ones.
> to me, a rowlock is sufficient, and fine grained enough, and the hold lock
> holds it to the end of transaction.
> Am I right?

Difficult to say with the small amount of information, but it does not
seem quite right.

The smallest change you should do is to is to remove ROWLOCK, but insert
UPDLOCK instead. ROWLOCK is sort of meaningless. Either you have a good
index to locate the rows, and you will get rowlocks. Or you don't have
good indexes, and SQL Server will have to lock the entire table.

UPDLOCK is a shared lock that does not block other readers, but it
blocks others that try to use UPDLOCK. With only HOLDLOCK and you have
two processes coming to the place at the same time, will lock the same
10 ten rows, and then when they to delete, they will block each other.

But there may be other things you could consider. It could be the case
that application locks are a better choice. An application lock is a
lock on a user-defined resource (that is a text string) which is handled
by the Lock Manager. But I know too little about your application to
tell whether it would fit it here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

read sql page data

can i look rows(data) from sql server data page
i can learn page information using dbcc page(...)
but i have to read data rows from page.
thanks
There are a number of print options for DBCC PAGE. Use the right one, and the data will be presented
in clear text. Experiment with print option 0, 1, 2 and 3.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks
|||Sabri,
Not sure what you're asking. Check out:
http://support.microsoft.com/kb/q83065/
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks
|||This article last applied to 6.5 but was updated in 2005 so I'm not sure if
it will with 7 or 2000. Might want to Google what DBCC PAGE or something
similar.
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks

read sql page data

can i look rows(data) from sql server data page
i can learn page information using dbcc page(...)
but i have to read data rows from page.
thanksThere are a number of print options for DBCC PAGE. Use the right one, and th
e data will be presented
in clear text. Experiment with print option 0, 1, 2 and 3.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks|||Sabri,
Not sure what you're asking. Check out:
http://support.microsoft.com/kb/q83065/
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks|||This article last applied to 6.5 but was updated in 2005 so I'm not sure if
it will with 7 or 2000. Might want to Google what DBCC PAGE or something
similar.
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks

read sql page data

can i look rows(data) from sql server data page
i can learn page information using dbcc page(...)
but i have to read data rows from page.
thanksThere are a number of print options for DBCC PAGE. Use the right one, and the data will be presented
in clear text. Experiment with print option 0, 1, 2 and 3.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks|||Sabri,
Not sure what you're asking. Check out:
http://support.microsoft.com/kb/q83065/
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks|||This article last applied to 6.5 but was updated in 2005 so I'm not sure if
it will with 7 or 2000. Might want to Google what DBCC PAGE or something
similar.
HTH
Jerry
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:1B2B5D9B-4AE4-46D6-BD01-6CC9AB89A19C@.microsoft.com...
> can i look rows(data) from sql server data page
> i can learn page information using dbcc page(...)
> but i have to read data rows from page.
> thanks

Read CSV file - Save Columns into rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Amar:

I think for what you are describing I would use SSIS; if you are running SQL Server 2000, that would be DTS instead of SSIS. Other alternatives include the use of OPENROWSET, BULK INSERT, or BCP. Read about these alternatives in books online and choose the alternative that you think best fits.

Dave

|||

Hi Amar,

refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx also.

Hemantgiri S. Goswami

sql

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on
Customer count(2nd record in each row of CSV file) and save to SQL
table
--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
Total_3..can go upto 350
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
Brown',17.00,'Rick Davis',18.00
Data in SQL table from csv file should look like this
State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00
I have multiple CSV files with millions of records.What is the best and
fastest way to achieve this?
Throw me anything
Hi
"pintoo" wrote:

> I want to import CSV file and convert columns into rows depending on
> Customer count(2nd record in each row of CSV file) and save to SQL
> table
> --CSV file format
> State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
> Total_3..can go upto 350
> GA,2,'John Doe',14.00,'Roger Smith',15.00
> FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
> SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
> Brown',17.00,'Rick Davis',18.00
> Data in SQL table from csv file should look like this
> State,Name,Total
> GA,John Doe,14.00
> GA,Roger Smith,15.00
> FL,John Doe,14.00,
> FL,Roger Smith,15.00
> FL,Sally Cox,16.00
>
If you are moving these into rows the data is not normalized! You will only
be able to have a finite number of rows that can be converted, therefore you
could self join if each row is allocated a sequence number or the methid
described by Erland in
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/250f0c68596ce22e?&hl=en

> I have multiple CSV files with millions of records.What is the best and
> fastest way to achieve this?
> Throw me anything
>
If you are using SQL 2005 you could use the PIVOT command.
John

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Perhaps you should look at creating a VB/C# application to handle this situation.|||

I am trying do something very similar to what you are asking...I found import wizard by right clicking on the database itself. Under the "Tasks" option. I just got this administrators handbook which is somewhat helpful. First, you will have to import so it seems the column names first there is a check box on the screen you need to select to get the column names to appear this helps out with the data import too. Then go back from the top and do the same thing to get the data. You need to arrive at the copy or query screen to handle this. And choose the option to write a query. For, me however when I go back for the second pass through. It doesn't seem to let me get to the screen again, I saw it on the initial pass. Let me know if this worked for you?

|||

Once you have imported the rows to a temp. table then you might need to run a self-join to extract the required columns like:

insert into TableB(column1,column2,column3)
select A.column1 , B.column1, C.column1
from
(select column1 from TableA where column1 like 'A%') A
cross join (select column1 from TableA where column1 like 'B%') B
cross join (select column1 from TableA where column1 like 'C%') C
order by A.column1 , B.column1, C.column1

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Perhaps you should look at creating a VB/C# application to handle this situation.|||

I am trying do something very similar to what you are asking...I found import wizard by right clicking on the database itself. Under the "Tasks" option. I just got this administrators handbook which is somewhat helpful. First, you will have to import so it seems the column names first there is a check box on the screen you need to select to get the column names to appear this helps out with the data import too. Then go back from the top and do the same thing to get the data. You need to arrive at the copy or query screen to handle this. And choose the option to write a query. For, me however when I go back for the second pass through. It doesn't seem to let me get to the screen again, I saw it on the initial pass. Let me know if this worked for you?

|||

Once you have imported the rows to a temp. table then you might need to run a self-join to extract the required columns like:

insert into TableB(column1,column2,column3)
select A.column1 , B.column1, C.column1
from
(select column1 from TableA where column1 like 'A%') A
cross join (select column1 from TableA where column1 like 'B%') B
cross join (select column1 from TableA where column1 like 'C%') C
order by A.column1 , B.column1, C.column1

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

You can use something like this..

Create Table #DataIntoRows
(
State Varchar(10),
Name Varchar(100),
Total Int
)
SELECT * INTO #Data FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir={PATH}','SELECT * FROM Data.csv')

Declare @.I as Int;
Declare @.Count as Int;
Select
@.I = 1,
@.Count=Count(Colid)
From
tempdb..Syscolumns
Where
id = Object_Id('tempdb..#Data') and Colid > 2;

While @.I < @.Count
begin
Declare @.SQL as Varchar(300);
Select @.SQL = 'Insert Into #DataIntoRows Select State,'
Select @.SQL = @.SQL + name From tempdb..Syscolumns Where id = Object_Id('tempdb..#Data') and colId= @.I + 2
Select @.SQL = @.SQL + ',' + name + ' From #Data Where ' + name + ' Is NOT NULL' From tempdb..Syscolumns Where id = Object_Id('tempdb..#Data') and colId= @.I + 3
Exec(@.SQL)
Select @.I = @.I + 2;
End

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

See if my post in this thread helps get you started. I know it's not quite the same, but it may be a good foundation for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880588&SiteID=1

Phil|||

Thanks Phil for your reply

I am new to SSID can you please provide some more detail

|||You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.|||

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.|||

Phil Brammer wrote:

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

|||

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?|||

Phil Brammer wrote:

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?

No it won't But then the complexity here is in the source file - there's no getting around that. The original poster will just have to accept that he has a ridiculously complex source file and bite the bullet. There are still lots of ways to achieve this.

-Jamie

|||

Can you guys please point me to any examples or blogs.

|||

Amar Khaira wrote:

Can you please point me any examples or blogs

One way is to search the forums for examples. On the main page of this forum is a search box on the left-hand side of the screen. Type in unpivot and you'll get a few examples/topics that discuss that transformation.|||

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

|||

Brian Knight wrote:

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

How can the variable columns not be a problem? Because the flat file source won't account for the variability, you'll have to read in each record as one column, and then split it with a script transformation. In doing that, you might be able to set a fixed number of columns and fill them with NULLs if no data exists. I'm trying to understand how you think you can expose all of the columns to the unpivot transformation when the metadata can't be pre-populated with certainty. (That is, based on my previous example, there's a certain row limit that the flat file connector uses to assess how many columns there are in the file.)|||

I am getting following error while trying Unpivot Transform:

PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

See if my post in this thread helps get you started. I know it's not quite the same, but it may be a good foundation for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880588&SiteID=1

Phil|||

Thanks Phil for your reply

I am new to SSID can you please provide some more detail

|||You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.|||

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.|||

Phil Brammer wrote:

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

|||

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?|||

Phil Brammer wrote:

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?

No it won't But then the complexity here is in the source file - there's no getting around that. The original poster will just have to accept that he has a ridiculously complex source file and bite the bullet. There are still lots of ways to achieve this.

-Jamie

|||

Can you guys please point me to any examples or blogs.

|||

Amar Khaira wrote:

Can you please point me any examples or blogs

One way is to search the forums for examples. On the main page of this forum is a search box on the left-hand side of the screen. Type in unpivot and you'll get a few examples/topics that discuss that transformation.|||

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

|||

Brian Knight wrote:

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

How can the variable columns not be a problem? Because the flat file source won't account for the variability, you'll have to read in each record as one column, and then split it with a script transformation. In doing that, you might be able to set a fixed number of columns and fill them with NULLs if no data exists. I'm trying to understand how you think you can expose all of the columns to the unpivot transformation when the metadata can't be pre-populated with certainty. (That is, based on my previous example, there's a certain row limit that the flat file connector uses to assess how many columns there are in the file.)|||

I am getting following error while trying Unpivot Transform:

PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.

Read CSV file - Save Columns into rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

Amar:

I think for what you are describing I would use SSIS; if you are running SQL Server 2000, that would be DTS instead of SSIS. Other alternatives include the use of OPENROWSET, BULK INSERT, or BCP. Read about these alternatives in books online and choose the alternative that you think best fits.

Dave

|||

Hi Amar,

refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx also.

Hemantgiri S. Goswami

Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on
Customer count(2nd record in each row of CSV file) and save to SQL
table
--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
Total_3..can go upto 350
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
Brown',17.00,'Rick Davis',18.00
Data in SQL table from csv file should look like this
State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00
I have multiple CSV files with millions of records.What is the best and
fastest way to achieve this?
Throw me anythingHi
"pintoo" wrote:

> I want to import CSV file and convert columns into rows depending on
> Customer count(2nd record in each row of CSV file) and save to SQL
> table
> --CSV file format
> State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3,
> Total_3..can go upto 350
> GA,2,'John Doe',14.00,'Roger Smith',15.00
> FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
> SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James
> Brown',17.00,'Rick Davis',18.00
> Data in SQL table from csv file should look like this
> State,Name,Total
> GA,John Doe,14.00
> GA,Roger Smith,15.00
> FL,John Doe,14.00,
> FL,Roger Smith,15.00
> FL,Sally Cox,16.00
>
If you are moving these into rows the data is not normalized! You will only
be able to have a finite number of rows that can be converted, therefore you
could self join if each row is allocated a sequence number or the methid
described by Erland in
http://groups-beta.google.com/group...r />
2e?&hl=en

> I have multiple CSV files with millions of records.What is the best and
> fastest way to achieve this?
> Throw me anything
>
If you are using SQL 2005 you could use the PIVOT command.
John

Read Consistency

What is the mechanism used by select statements to return point in time data?

I have a test setup - table t1 has 1000000 rows. A query (call q1) that selects all the rows (in NOLOCK mode and process them) takes 10 minutes. At the same time another process inserts another 1000000 rows into the same table t1. As expected, client that issued query q1 sees just 1000000 rows.

My understanding is that NOLOCK does not hold any locks. So, how did SQL Server know that it should not return the rows that are inserted after I issued the query q1? Some explanation or link to some whitepapers would be helpful.

Thanks

Unless you use the row-level versioning that was introduced in SQL Server 2005, there is no notion of 'point-in-time' data for the locking-based database engines for a certain fixed time in the past, until the transaction commits and only for the precise subset of the data read or written by the transaction and of the data that was intended to be read but it didn't exist when the transaction tried to access it.

Instead, the engine can provide an illusion of a serialized execution as if during the processing of transactions the view of the data accessed and touched by the transaction was 'frozen', i.e. the things that other transactions did, either occurred in the past, will occur in the future, or they don't matter if other transaction never read or wrote the data accessed and touched by our transaction. As a result the data that is seen by a single transaction ultimately can be viewed as a consistent slice of some relevant subset of entire database as of 'now' while the transaction is active and as of commit time after the transaction commits.

Having said that I realize that it might sound really cryptic and confusing - but this is how the things are done in the locking-based transaction processing systems. If you come from the Oracle world it will take some time to adjust to a different paradigm.

|||

Thanks Tengiz but ...

In my scenario, let us say that query q1 started at 10 AM and finished at 10:10 AM. At 10 AM it had 1000000 rows and by 10:05 AM other transactions inserted 1000000 rows more making it a total of 2000000 rows. Why did not SQL server return 2000000 rows to q1 client? Somehow SQL server knew to provide the rows that existed at 10 AM (that I call point in time data; may be wrong terminology?) and ignore the rows that were added after that point. What is the internal mechanism used by the engine to give that illusion? I am inclined to think that even though it does not create locks it might create some sort of semaphores, latches or tables in memory to keep track of the rows that need to be returned to q1 client. As you guessed, I am from Oracle background; may be you answered my question and it might take little longer to get it.

|||

Could you please be more specific describing you scenario? The fact that the query only returned the initial set of rows and didn't see the rows that were inserted after the query started does not really mean that the server somehow knows or takes into account the time when the rows were inserted.

Again, the things are different if you use the row-level versioning - you either do it be switching to the snapshot isolation (after enabling it for the database) or if you allow versioning-based read-committed isolation.

But assuming the you don't use the row-level versioning, depending on the existing indexes, the actual query plans, the key values that existed in the table before the insert and the key values inserted, the select query with the right timing could easily skip the newly inserted records, but it would have nothing to do with the read-consistency provided to the row-level versioning.

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

The database is not setup to use row versioning. Also, this is a data warehouse system. So, DML statements can come only from ETL. No other concurrent user is touching this table while this program is running. However my SSIS program that maintains this table opens two sessions (a reader and a writer) as I explained in step 4. My concern is that these two sessions stepping on each other.

-

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

-

Step 1:

CREATE TABLE Table1 (

Column1[int] NOT NULL,

Column2[int] NOT NULL,

Column3[bit] NOT NULL,

Column4[datetime] NOT NULL,

Column5[int] NOT NULL,

Column6[int] NOT NULL,

Column7[varchar](255) NULL,

Column8[int] NULL,

Column9[int] NULL,

Column10[int] NULL

CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (

[Column1] ASC,

[Column2] ASC,

[Column4] ASC,

[Column5] ASC,

[Column6] ASC )

)

-

Step 2:

INSERT INTO Table1

SELECT *

FROM Source_Table1

10,556,214 rows inserted.

-

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads

delete

from SourceStage.RealDB_StatusHistory

where Column1 % 2 = 0

5,288,119 rows deleted

-

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted

5,268,095 rows updated

-

I repeated the above steps without the clustered index and I get the same row counts. The row counts show no surprises which leads me to believe that there is some kind of lock. I have to make sure this works 100% before I put this code into production. The documentation leads me to believe that it does not work 100% of the time. If that is the case, I should be able to simulate a scenario where I get whacky row counts.

Here is what I think the reason for not getting whacky row counts in my setup. (a) when the clustered index is in place, it rebalances the tree during the delete operation. So the inserted rows go into new pages and SQL Server somehow knows to ignore them. (b) when there is no clustered index, then it sorts the data in Temp database before it is being fed to the SSIS. So data is essentially is sourced from the Temp database during pipeline operation of SSIS. Am I thinking in the right direction?

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

A quick answer to your question "can I use NOLOCK without any bad effects in this scenario?" is NO. The NOLOCK hint relaxes certain concurrency-related guarantees in the engine and essentially nullifies the notion of transactional consistency. It doesn’t mean that you will never get any consistency if you use NOLOCK, but you will not in general have predictable results.

I'm not an SSIS expert, so I'm not sure how SSIS really performs the 'insert else update' operation and I still don't quite get what you actually do in this scenario. But from the description of it looks like the plan does include spooling in tempdb for sort - the fast load option normally feeds data in through the BCP API which if the destination table is a clustered index assumes that that data needs to be sorted before it gets delivered to the destination. Hence, if the input provided by the SSIS is not sorted (there is a special hint that SSIS can specify in order to avoid extra sort) the then query optimizer adds the sort operator.

Spooling can certainly make it look like there indeed was some kind of 'read consistency' provided, but, again, depending on the actual query and specific conditions the optimizer is free to choose other options as well.

|||Thanks for the reply. Thinking about it further, when I don't use the NOLOCK hint, my SSIS package just waits forever. When I use NOLOCK hint, it seems to work fine. However, what if SQL Server does not honour the NOLOCK hint? My package might wait forever. So, I decided use the Lookup Transformation of SSIS rather than the Merge Join Transformation. Lookup Transformation can cache the data upfront before the Data Flow Task starts prosessing source rows. This way there is no contention.

Read Consistency

What is the mechanism used by select statements to return point in time data?

I have a test setup - table t1 has 1000000 rows. A query (call q1) that selects all the rows (in NOLOCK mode and process them) takes 10 minutes. At the same time another process inserts another 1000000 rows into the same table t1. As expected, client that issued query q1 sees just 1000000 rows.

My understanding is that NOLOCK does not hold any locks. So, how did SQL Server know that it should not return the rows that are inserted after I issued the query q1? Some explanation or link to some whitepapers would be helpful.

Thanks

Unless you use the row-level versioning that was introduced in SQL Server 2005, there is no notion of 'point-in-time' data for the locking-based database engines for a certain fixed time in the past, until the transaction commits and only for the precise subset of the data read or written by the transaction and of the data that was intended to be read but it didn't exist when the transaction tried to access it.

Instead, the engine can provide an illusion of a serialized execution as if during the processing of transactions the view of the data accessed and touched by the transaction was 'frozen', i.e. the things that other transactions did, either occurred in the past, will occur in the future, or they don't matter if other transaction never read or wrote the data accessed and touched by our transaction. As a result the data that is seen by a single transaction ultimately can be viewed as a consistent slice of some relevant subset of entire database as of 'now' while the transaction is active and as of commit time after the transaction commits.

Having said that I realize that it might sound really cryptic and confusing - but this is how the things are done in the locking-based transaction processing systems. If you come from the Oracle world it will take some time to adjust to a different paradigm.

|||

Thanks Tengiz but ...

In my scenario, let us say that query q1 started at 10 AM and finished at 10:10 AM. At 10 AM it had 1000000 rows and by 10:05 AM other transactions inserted 1000000 rows more making it a total of 2000000 rows. Why did not SQL server return 2000000 rows to q1 client? Somehow SQL server knew to provide the rows that existed at 10 AM (that I call point in time data; may be wrong terminology?) and ignore the rows that were added after that point. What is the internal mechanism used by the engine to give that illusion? I am inclined to think that even though it does not create locks it might create some sort of semaphores, latches or tables in memory to keep track of the rows that need to be returned to q1 client. As you guessed, I am from Oracle background; may be you answered my question and it might take little longer to get it.

|||

Could you please be more specific describing you scenario? The fact that the query only returned the initial set of rows and didn't see the rows that were inserted after the query started does not really mean that the server somehow knows or takes into account the time when the rows were inserted.

Again, the things are different if you use the row-level versioning - you either do it be switching to the snapshot isolation (after enabling it for the database) or if you allow versioning-based read-committed isolation.

But assuming the you don't use the row-level versioning, depending on the existing indexes, the actual query plans, the key values that existed in the table before the insert and the key values inserted, the select query with the right timing could easily skip the newly inserted records, but it would have nothing to do with the read-consistency provided to the row-level versioning.

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

The database is not setup to use row versioning. Also, this is a data warehouse system. So, DML statements can come only from ETL. No other concurrent user is touching this table while this program is running. However my SSIS program that maintains this table opens two sessions (a reader and a writer) as I explained in step 4. My concern is that these two sessions stepping on each other.

-

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

-

Step 1:

CREATE TABLE Table1 (

Column1[int] NOT NULL,

Column2[int] NOT NULL,

Column3[bit] NOT NULL,

Column4[datetime] NOT NULL,

Column5[int] NOT NULL,

Column6 [int] NOT NULL,

Column7[varchar](255) NULL,

Column8[int] NULL,

Column9[int] NULL,

Column10[int] NULL

CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (

[Column1] ASC,

[Column2] ASC,

[Column4] ASC,

[Column5] ASC,

[Column6] ASC )

)

-

Step 2:

INSERT INTO Table1

SELECT *

FROM Source_Table1

10,556,214 rows inserted.

-

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads

delete

from SourceStage.RealDB_StatusHistory

where Column1 % 2 = 0

5,288,119 rows deleted

-

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted

5,268,095 rows updated

-

I repeated the above steps without the clustered index and I get the same row counts. The row counts show no surprises which leads me to believe that there is some kind of lock. I have to make sure this works 100% before I put this code into production. The documentation leads me to believe that it does not work 100% of the time. If that is the case, I should be able to simulate a scenario where I get whacky row counts.

Here is what I think the reason for not getting whacky row counts in my setup. (a) when the clustered index is in place, it rebalances the tree during the delete operation. So the inserted rows go into new pages and SQL Server somehow knows to ignore them. (b) when there is no clustered index, then it sorts the data in Temp database before it is being fed to the SSIS. So data is essentially is sourced from the Temp database during pipeline operation of SSIS. Am I thinking in the right direction?

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

A quick answer to your question "can I use NOLOCK without any bad effects in this scenario?" is NO. The NOLOCK hint relaxes certain concurrency-related guarantees in the engine and essentially nullifies the notion of transactional consistency. It doesn’t mean that you will never get any consistency if you use NOLOCK, but you will not in general have predictable results.

I'm not an SSIS expert, so I'm not sure how SSIS really performs the 'insert else update' operation and I still don't quite get what you actually do in this scenario. But from the description of it looks like the plan does include spooling in tempdb for sort - the fast load option normally feeds data in through the BCP API which if the destination table is a clustered index assumes that that data needs to be sorted before it gets delivered to the destination. Hence, if the input provided by the SSIS is not sorted (there is a special hint that SSIS can specify in order to avoid extra sort) the then query optimizer adds the sort operator.

Spooling can certainly make it look like there indeed was some kind of 'read consistency' provided, but, again, depending on the actual query and specific conditions the optimizer is free to choose other options as well.

|||Thanks for the reply. Thinking about it further, when I don't use the NOLOCK hint, my SSIS package just waits forever. When I use NOLOCK hint, it seems to work fine. However, what if SQL Server does not honour the NOLOCK hint? My package might wait forever. So, I decided use the Lookup Transformation of SSIS rather than the Merge Join Transformation. Lookup Transformation can cache the data upfront before the Data Flow Task starts prosessing source rows. This way there is no contention.sql

rc:Section not working on Matrix report with SQL RS 2000

Hi
I've got a SQL RS 2000 report which contains a matrix that sometimes
contains over 100 rows which spans more than one vertical printed page
and I want the whole report to show on one page when viewed via the web
so that the user can see the whole report displayed in one view rather
than them having to page to different sections.
I was expecting the report to render all on one page with the
rc:Section specified as zero but the rc:Section parameter seems to be
ignored and only the first page of the report is shown. I've specified
rc:Section=0 in the URL access parameters and I have the report toolbar
showing as I want the user to be able to preview/print etc.
This only seems to be a problem with the matrix report. Has anyone come
across this, or does anyone know of a workaround?
JonThis is the full url device param list in case there's a collision
somewhere:
rs:Command=Render
rs:Format=HTML4.0
rs:ClearSession=False
rc:Toolbar=True
rc:Zoom=100
rc:HTMLFragment=true
rc:Section=0
rc:Parameters=False
rc:LinkTarget=_self
Jon

rating system

this is probably a simple sql solution, but i could use some help.
i've got one table that has rows of documents, and another table with
any number of ratings for each of the documents. The rating table is
linked with an id column. the issue i am running up against is
returning a single average rating for a document.

SELECT document.title, (SELECT AVG(scale)
FROM Rating, document
WHERE
document.pkDocumentId = rating.documentId)
FROM Document

returns the same avg for all the ratings.

So i'd ideally like to return the values of the document columns
(tableA.*), with an additional column containing the average rating of
that document (avg(tableB.Scale) where pkDocumentId = documentId).

thanks in advance.SELECT D.title, AVG(R.scale)
FROM Document AS D
JOIN Rating AS R
ON D.pkdocumentid = R.documentid
GROUP BY D.pkdocumentid, D.title

--
David Portas
SQL Server MVP
--