bulk insert into partition view (sql 2000)

Hi, experts
I have ~ 50 table with the same structure in 4 databases. These tables are
union'ed in partition view.
Insert into partition view works perfect. I would like to load data into PV
with bulk insert from text file. But there is the restriction of PV - I
cannot do the bulk load into PV.
Then I created 3'rd table - and created the "instaed of insert" trigger.
This trigger do the insert into PV from inserted table. But I get the same
error.
Is it possible to implement insert into PV from text file using sql 2000 or
even sql 2005. Thanks in advance.
Ramunas BalukonisRamunas
ad for as I know, you should be able to do from bulk file also. Your problem
prablbly be with constraints. see especially that all NOT NULL columns are
updated and .txt is in proper format.
--
Regards
R.D
--Knowledge gets doubled when shared
"Ramunas Balukonis" wrote:

> Hi, experts
> I have ~ 50 table with the same structure in 4 databases. These tables are
> union'ed in partition view.
> Insert into partition view works perfect. I would like to load data into P
V
> with bulk insert from text file. But there is the restriction of PV - I
> cannot do the bulk load into PV.
> Then I created 3'rd table - and created the "instaed of insert" trigger.
> This trigger do the insert into PV from inserted table. But I get the same
> error.
> Is it possible to implement insert into PV from text file using sql 2000 o
r
> even sql 2005. Thanks in advance.
> Ramunas Balukonis
>
>|||RD, thanks for answer!
but when I do bulk insert into table directly, bulk insert success! So, the
problem is about bulk inserting into PV. Now I'm looking for a workaround.
Thanks
Ramunas
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> Ramunas
> ad for as I know, you should be able to do from bulk file also. Your
problem
> prablbly be with constraints. see especially that all NOT NULL columns are
> updated and .txt is in proper format.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ramunas Balukonis" wrote:
>
are
PV
same
or|||I found solution!
I do insert into PV using linked servers!
Ramunas
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1128423828.988065@.loger.vpmarket.int...
> RD, thanks for answer!
> but when I do bulk insert into table directly, bulk insert success! So,
the
> problem is about bulk inserting into PV. Now I'm looking for a workaround.
> Thanks
> Ramunas
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> problem
are
> are
into
> PV
I
trigger.
> same
2000
> or
>

Bulk insert in to a partitioned View?

Greetings once again my SQL friends,

I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :

Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation

How to solve this problem?

Hi,

Bulk insert operations are not supported for partitioned views. See for more details:

Exporting Data from or Importing Data to a View
http://msdn2.microsoft.com/en-us/ms187086.aspx

You can import Data within a Data Flow Task into a Partitioned View if you use an OLE DB Destination with the Data Access Mode option set to Table or View instead of "Table or View - fastload", which is the default and technically a bulk operation.

Please be aware that locally partitioned views are supported in SQL Server 2005 only for backward compatibility. See also:

Scenarios for Using Views
http://msdn2.microsoft.com/en-us/library/ms188250.aspx

I hope that helps,

Bertil

Bulk insert in SSIS

Has anyone else had this problem?

I am using 'OLE DB Destination' task in a data flow. When I select 'Table or view - fast load', check 'Table lock' and 'Check constraints' and run the flow, I get only one row inserted. The data flow edge shows hundreds of thousands of rows flowing to destination and the task completes (turns green).

The problem goes away if I uncheck bulk load (e.g., select 'Table or view').

Is fast load using bulk insert or some internal code?

?

That is weird. Have you tried to set a value for 'Maximux insert commit size'?

As far as I know SSIS uses bulk inserts when fast load is selected.

Use SQL Server profiler to see the databse activity while the package is being run and insepect the results of the progress tab during the debug session...

Other than that; no idea

Rafael Salas

Building View Dynamically .. Need Help!

This is my resultset
Value Name
1 A
101 A
2 B
10 B
70 B
But I want it in this way from SQL Server
A B
1 2
101 10
70hangar18 wrote:
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
You should probably do this in your client application, but, run this script
in query analyzer:
set nocount on
select 1 Value,'A' [Name] into #temp
union all select
101 ,'A'
union all select
2 ,'B'
union all select
10 ,'B'
union all select
70 ,'B'
select * from #temp
Select
ta.A,
tb.B
From
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [A]
FROM #temp t1
WHERE t1.Name='A') ta
Full outer join
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [B]
FROM #temp t1
WHERE t1.Name='B') tb
ON ta.ID=tb.ID
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE TABLE #Test
(
col INT,
col1 CHAR(1)
)
INSERT INTO #Test VALUES (1,'A')
INSERT INTO #Test VALUES (20,'A')
INSERT INTO #Test VALUES (100,'A')
INSERT INTO #Test VALUES (10,'B')
INSERT INTO #Test VALUES (5,'B')
INSERT INTO #Test VALUES (3,'B')
SELECT
CASE WHEN col1 ='A' THEN col END AS 'A',
CASE WHEN col1 ='B' THEN col END AS 'B'
FROM #Test
"hangar18" <soni.somarajan@.wipro.com> wrote in message
news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
>|||That was my first thought as well, but it results in:
A B
1 [NULL]
101 [NULL]
[NULL] 2
[NULL] 10
[NULL] 70
Not quite what the OP wants.
Bob
Uri Dimant wrote:
> CREATE TABLE #Test
> (
> col INT,
> col1 CHAR(1)
> )
> INSERT INTO #Test VALUES (1,'A')
> INSERT INTO #Test VALUES (20,'A')
> INSERT INTO #Test VALUES (100,'A')
> INSERT INTO #Test VALUES (10,'B')
> INSERT INTO #Test VALUES (5,'B')
> INSERT INTO #Test VALUES (3,'B')
>
> SELECT
> CASE WHEN col1 ='A' THEN col END AS 'A',
> CASE WHEN col1 ='B' THEN col END AS 'B'
> FROM #Test
>
>
>
> "hangar18" <soni.somarajan@.wipro.com> wrote in message
> news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 1 Dec 2005 05:36:56 -0800, hangar18 wrote:

>This is my resultset
>Value Name
>1 A
>101 A
>2 B
>10 B
>70 B
>But I want it in this way from SQL Server
>A B
>1 2
>101 10
> 70
Hi hangar18,
Bob's post will work. But just for fun, here's another possibility:
SELECT MAX(A) AS A, MAX(B) AS B
FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
CASE WHEN a.col1 ='B' THEN a.col END AS B,
(SELECT COUNT(*)
FROM #Test AS b
WHERE b.col1 = a.col1
AND b.col <= a.col) AS Rank
FROM #Test AS a) AS d
GROUP BY Rank
ORDER BY Rank
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The basic principle of a tiered architecture is that display and
formatting is done in the front end and ** never** in the back end.
This a more basic programming principle than just SQL and RDBMS. This
should have been covered in the first year of your comp sci courses.|||When are you going to learn, perhaps you should actually do some
programming!
Formatting should be done where it is most efficient to do it, you can not
blankly state formatting be done in the front end and **never** the backend
its just not true - any programmer knows that.
Data manipulation is best done in the SQL Server and may well include
formatting, consider - paging, pivoting, security to name but a couple.
I think it is very irresponsible for you to keep taking this line when so
many times myself and other posters have given plenty of examples of when to
do formatting in the engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133490026.932802.161270@.o13g2000cwo.googlegroups.com...
> The basic principle of a tiered architecture is that display and
> formatting is done in the front end and ** never** in the back end.
> This a more basic programming principle than just SQL and RDBMS. This
> should have been covered in the first year of your comp sci courses.
>|||Hugo Kornelis wrote:
> SELECT MAX(A) AS A, MAX(B) AS B
> FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
> CASE WHEN a.col1 ='B' THEN a.col END AS B,
> (SELECT COUNT(*)
> FROM #Test AS b
> WHERE b.col1 = a.col1
> AND b.col <= a.col) AS Rank
> FROM #Test AS a) AS d
> GROUP BY Rank
> ORDER BY Rank
>
Clever!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

building models with views

Using data in SS2000 and RS2005. I tried to build a model from a view that I
created. I got the "tables does not have a primary key" error. Do I have to
use indexed views in the model?
--
Dan D.Dan,
You will need to specify a 'logical' primary key in your DSV (Data Source
View)
Bret
"Dan D." wrote:
> Using data in SS2000 and RS2005. I tried to build a model from a view that I
> created. I got the "tables does not have a primary key" error. Do I have to
> use indexed views in the model?
> --
> Dan D.

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...
> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA
.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
KeyExt Key
150K
273J
375K
460A
Table 2
KeyValue
50KABC
60ADEF
75KGHI
Current Join on SMC
Key ExtKey Value
150KABC
273J(null)
375KGHI
460ADEF
Desired Join result
Key ExtKey Value
150KABC
273JOther
375KGHI
460ADEF
I've also tried updating the view but that didn't work for me either.
TIA
Bill
Bill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill