Dear all,
I want to insert 10 rows data as follows
column-A column-B column-C
A A
1
A A
2
A A
3
A A
4
A A
5
In each row, the Column-A and column-B are the same. While column-c
will increase from 1 to 10.
How can I do that in one insert statement . Or I need to loop in VB
Program.
Thanks.If column C is an identity problem, then you just insert 'a', 'a' 10 times
(like your other question).
But otherwise, you could use rowcount and row_number like this:
set rowcount 10
insert into newtable
select 'a', 'a', row_number() over (order by quantity)
from sometable
where sometable has at least 10 rows.
Or, using a CTE instead of rowcount:
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable
)
insert into newtable select * from rownums where rownum <= 10
"hon123456" wrote:
> Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
> In each row, the Column-A and column-B are the same. While column-c
> will increase from 1 to 10.
> How can I do that in one insert statement . Or I need to loop in VB
> Program.
> Thanks.
>|||Thanks Rob.
When I try to run
select 'a', 'a', row_number() over (order by quantity) from sometable.
The query analyzer complains 'row_number' is not a recognized function
name.
By the way, can I have the complete sql statement for the follows
statement
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable )|||Thanks Rob. sorry to post again. But I am wondering what does the
variable "quantity" come from in the following statement:
select 'a', 'a', row_number() over (order by quantity) from sometable.|||Haha - sorry... that's just one of the columns in 'sometable'. I meant to
change that.
If it's not recognising row_number(), then perhaps you're not running
SQL2005 ? :(
It's somewhat harder to do in SQL2000. Off the top of my head, the easiest
way to do it is to create a temporary table with an identity field, or
perhaps use a while loop and do 10 single inserts (after all, if it's only
10...).
One nice way would be to use an auxiliary table of numbers. You'll find it
useful for all kinds of other reasons too... but most of all, you can easily
just say "select 'a', 'a', num from nums where num <= 10"
Hope this helps,
Rob
"hon123456" wrote:
> Thanks Rob. sorry to post again. But I am wondering what does the
> variable "quantity" come from in the following statement:
> select 'a', 'a', row_number() over (order by quantity) from sometable.
>|||If you already had a table of sequential numbers this would be
simpler. You would simply replace derived table A with that table,
and limit the values to the range 1 to 10 in a WHERE clause.
INSERT SomeTable
SELECT A.column_a, 'A' as column_b, 'A' as column_c
FROM (select 1 as column_a UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9 UNION
select 10) as A
Roy Harvey
Beacon Falls, CT
On 18 Apr 2006 21:48:17 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:
>Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
>In each row, the Column-A and column-B are the same. While column-c
>will increase from 1 to 10.
>How can I do that in one insert statement . Or I need to loop in VB
>Program.
>Thanks.
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Friday, March 30, 2012
how to insert sp_helptext output into a table?
hi Freinds,
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML
How to insert multiple rows using stored procedure
How to insert multiple rows with using a single stored procedure and favourably as an atomic process?You need to inlude SAVE POINT in your T-SQL code so a rollback or interruption will not take the Transaction back to the beginnning. Like the sample below. Hope this helps.
SAVE TRANSACTION SavepointName
IF @.@.error= some Error
BEGIN
ROLLBACK TRANSACTION SavepointName
COMMIT TRANSACTION
END
Kind regards,
Gift Peddie|||You have a couple of options here:
1) created a delimited key,value pair and parse it in the proc
2) package the values as an xml chunk and use OPENXML to shred the doc and perform the insert
I prefer option 2.
And with regards to atomicity, you wrap 1 or 2 in a BEGIN TRAN, COMMIT or ABORT in the proc.|||Since I never used OPEN XML can you give me a link to a good tutorial how to pass xml from .net code to sql sp..
Thanks|||Have a look at the following article:
Decomposing with OpenXML
How to insert into Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table
Maybe something like?
|||insert into #TempResult
select distinct
Memberid,
Month,
Year
from Rebate a
where not exists
( select 0 from #TempResult b
where a.memberid = b.memberid
and a.month = b.month
and a.year = b.year
)Dave
If you are using SQL Server 2005, you can also use the EXCEPT operator:
insert into #TempResult
select Memberid,
Month,
Year
from Rebate
except
select Memberid,
Month,
Year
from #TempResult
Lax:
You do not; I choose zero because the column selected in this case doesn't matter; what matters is whether or not the row exists. This is a semi-join? Can somebody confirm the semi-join?
|||( Maybe a left anti semi join )Dave
Monday, March 26, 2012
how to insert a huge data from mdb to sql 2000 ?
I want to migrate my access data to sql 2000 server, but
the prolems are :
1. my access data is very huge (hundreds thousands rows),
can I use bcp or bulk insert to sql 2000 from ms access ?
2. Can we performs inserting data without recorded in
transaction log ?
thanks.Hundreds of thousands of rows is not an insignificant amount but likewise is
is not really an amount that should caue you concern.
A number of methods to move the data
1. Export to text file from Access and import using either BULK INSERT or
bcp
2. DTS
3. Access upsizing wizard.
4. Linked server to Access
All things are recorded in the log but to varying amounts. Look up RECOVERY
MODELs in BOL.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||KRESNA
For now days hundreds thousands rows i would not called very huge table.
> 2. Can we performs inserting data without recorded in
> transaction log ?
You cannot , but you set recovery mode of the database to SIMPLE.
Also ,consider create linked server to the .mdb and perform
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
For more details please refer to BOL.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||Dear friends,
I already try to use dts to import data from access to sql
2000 , performance is slow, it takes more than 5 menits,
because this is part of reporting system, I am afraid user
will complain, I try to use linkserver to retrieve data
from ms access but peformance is not good too.
My friends tell me, using dts with active X scripting is
slow, is it true ?
By the way, thans for your sugestions, I will try to put
temporary table in differents db and use simple mode
recovery, I hope performance is better.
Thanks.
>--Original Message--
>KRESNA
>For now days hundreds thousands rows i would not called
very huge table.
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>You cannot , but you set recovery mode of the database to
SIMPLE.
>Also ,consider create linked server to the .mdb and
perform
>SELECT * FROM OPENQUERY( test_Access1, 'select * from
table1')
>For more details please refer to BOL.
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> I want to migrate my access data to sql 2000 server, but
>> the prolems are :
>> 1. my access data is very huge (hundreds thousands
rows),
>> can I use bcp or bulk insert to sql 2000 from ms
access ?
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>> thanks.
>
>.
>|||Kresna
Just a guess.
Try remove all indexes on .mdb database.
Also perhaps you need to transfer data when all users went home.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> Dear friends,
> I already try to use dts to import data from access to sql
> 2000 , performance is slow, it takes more than 5 menits,
> because this is part of reporting system, I am afraid user
> will complain, I try to use linkserver to retrieve data
> from ms access but peformance is not good too.
> My friends tell me, using dts with active X scripting is
> slow, is it true ?
> By the way, thans for your sugestions, I will try to put
> temporary table in differents db and use simple mode
> recovery, I hope performance is better.
> Thanks.
>
>
> >--Original Message--
> >KRESNA
> >For now days hundreds thousands rows i would not called
> very huge table.
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >You cannot , but you set recovery mode of the database to
> SIMPLE.
> >Also ,consider create linked server to the .mdb and
> perform
> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> table1')
> >
> >For more details please refer to BOL.
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> I want to migrate my access data to sql 2000 server, but
> >> the prolems are :
> >> 1. my access data is very huge (hundreds thousands
> rows),
> >> can I use bcp or bulk insert to sql 2000 from ms
> access ?
> >>
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >>
> >> thanks.
> >
> >
> >.
> >|||I have exactly near 750.000 rows, I gathered from joining
5 tables in Ms Access before insert them to sql 2000
server, I think you are right, I must delete indexes in
destination and may created them back after inserted, is
it better ? , but sorry I cannot remove indexes in Ms
Access.
>--Original Message--
>How many rows do you have exactly ?
>What is the structure of the table?
>Removing the following from the destination should see an
increase in
>performance
>1. Indexes
>2. Triggers
>Also setting the recovery model to Simple will help.
>You will need to reapply afterwards.
>
>Exporting to text file then reimporting will I dare say
be no quicker for
>you. This as has been suggested would ideally be carried
out out of hours
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
>> Kresna
>> Just a guess.
>> Try remove all indexes on .mdb database.
>> Also perhaps you need to transfer data when all users
went home.
>> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> > Dear friends,
>> >
>> > I already try to use dts to import data from access
to sql
>> > 2000 , performance is slow, it takes more than 5
menits,
>> > because this is part of reporting system, I am afraid
user
>> > will complain, I try to use linkserver to retrieve
data
>> > from ms access but peformance is not good too.
>> >
>> > My friends tell me, using dts with active X scripting
is
>> > slow, is it true ?
>> >
>> > By the way, thans for your sugestions, I will try to
put
>> > temporary table in differents db and use simple mode
>> > recovery, I hope performance is better.
>> >
>> > Thanks.
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >KRESNA
>> > >For now days hundreds thousands rows i would not
called
>> > very huge table.
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >You cannot , but you set recovery mode of the
database to
>> > SIMPLE.
>> > >Also ,consider create linked server to the .mdb and
>> > perform
>> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> > table1')
>> > >
>> > >For more details please refer to BOL.
>> > >
>> > >
>> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> > message
>> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> > >> I want to migrate my access data to sql 2000
server, but
>> > >> the prolems are :
>> > >> 1. my access data is very huge (hundreds thousands
>> > rows),
>> > >> can I use bcp or bulk insert to sql 2000 from ms
>> > access ?
>> > >>
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >>
>> > >> thanks.
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Uri, why should I remove indexes from ms access ? , I
think it is not relevan, I agree with suggestions from
Allan and you to remove indexes in destinations (SQL 2000)
because t-sql insert will update index too.
Thanks.
>--Original Message--
>Kresna
>Just a guess.
>Try remove all indexes on .mdb database.
>Also perhaps you need to transfer data when all users
went home.
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> Dear friends,
>> I already try to use dts to import data from access to
sql
>> 2000 , performance is slow, it takes more than 5 menits,
>> because this is part of reporting system, I am afraid
user
>> will complain, I try to use linkserver to retrieve data
>> from ms access but peformance is not good too.
>> My friends tell me, using dts with active X scripting is
>> slow, is it true ?
>> By the way, thans for your sugestions, I will try to put
>> temporary table in differents db and use simple mode
>> recovery, I hope performance is better.
>> Thanks.
>>
>>
>> >--Original Message--
>> >KRESNA
>> >For now days hundreds thousands rows i would not called
>> very huge table.
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >You cannot , but you set recovery mode of the database
to
>> SIMPLE.
>> >Also ,consider create linked server to the .mdb and
>> perform
>> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> table1')
>> >
>> >For more details please refer to BOL.
>> >
>> >
>> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> message
>> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> >> I want to migrate my access data to sql 2000 server,
but
>> >> the prolems are :
>> >> 1. my access data is very huge (hundreds thousands
>> rows),
>> >> can I use bcp or bulk insert to sql 2000 from ms
>> access ?
>> >>
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >>
>> >> thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||OK I see now.
Indexes on the Source will help you get to the rows you want (Providing they
are well placed).
Is it a complicated SELECT ?
Have you tried importing all the tables to SQL Server and then use a view
over them to do the inserts ?
How long does it take to Export using Access your Access data to Text File ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:05e401c37062$8dd4df30$a301280a@.phx.gbl...
> I have exactly near 750.000 rows, I gathered from joining
> 5 tables in Ms Access before insert them to sql 2000
> server, I think you are right, I must delete indexes in
> destination and may created them back after inserted, is
> it better ? , but sorry I cannot remove indexes in Ms
> Access.
> >--Original Message--
> >How many rows do you have exactly ?
> >What is the structure of the table?
> >
> >Removing the following from the destination should see an
> increase in
> >performance
> >
> >1. Indexes
> >2. Triggers
> >
> >Also setting the recovery model to Simple will help.
> >
> >You will need to reapply afterwards.
> >
> >
> >Exporting to text file then reimporting will I dare say
> be no quicker for
> >you. This as has been suggested would ideally be carried
> out out of hours
> >
> >
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
> >> Kresna
> >> Just a guess.
> >> Try remove all indexes on .mdb database.
> >> Also perhaps you need to transfer data when all users
> went home.
> >>
> >> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> > Dear friends,
> >> >
> >> > I already try to use dts to import data from access
> to sql
> >> > 2000 , performance is slow, it takes more than 5
> menits,
> >> > because this is part of reporting system, I am afraid
> user
> >> > will complain, I try to use linkserver to retrieve
> data
> >> > from ms access but peformance is not good too.
> >> >
> >> > My friends tell me, using dts with active X scripting
> is
> >> > slow, is it true ?
> >> >
> >> > By the way, thans for your sugestions, I will try to
> put
> >> > temporary table in differents db and use simple mode
> >> > recovery, I hope performance is better.
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> > >--Original Message--
> >> > >KRESNA
> >> > >For now days hundreds thousands rows i would not
> called
> >> > very huge table.
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >You cannot , but you set recovery mode of the
> database to
> >> > SIMPLE.
> >> > >Also ,consider create linked server to the .mdb and
> >> > perform
> >> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> > table1')
> >> > >
> >> > >For more details please refer to BOL.
> >> > >
> >> > >
> >> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> > message
> >> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> > >> I want to migrate my access data to sql 2000
> server, but
> >> > >> the prolems are :
> >> > >> 1. my access data is very huge (hundreds thousands
> >> > rows),
> >> > >> can I use bcp or bulk insert to sql 2000 from ms
> >> > access ?
> >> > >>
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >>
> >> > >> thanks.
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||ok
I did not think you have already database on SQL Server. I thought you are
plane phase yet.
So , I agree you need to remove all indexes and triggres on SQL Server
database.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:04da01c37063$5f92df40$a401280a@.phx.gbl...
> Uri, why should I remove indexes from ms access ? , I
> think it is not relevan, I agree with suggestions from
> Allan and you to remove indexes in destinations (SQL 2000)
> because t-sql insert will update index too.
> Thanks.
> >--Original Message--
> >Kresna
> >Just a guess.
> >Try remove all indexes on .mdb database.
> >Also perhaps you need to transfer data when all users
> went home.
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> Dear friends,
> >>
> >> I already try to use dts to import data from access to
> sql
> >> 2000 , performance is slow, it takes more than 5 menits,
> >> because this is part of reporting system, I am afraid
> user
> >> will complain, I try to use linkserver to retrieve data
> >> from ms access but peformance is not good too.
> >>
> >> My friends tell me, using dts with active X scripting is
> >> slow, is it true ?
> >>
> >> By the way, thans for your sugestions, I will try to put
> >> temporary table in differents db and use simple mode
> >> recovery, I hope performance is better.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >KRESNA
> >> >For now days hundreds thousands rows i would not called
> >> very huge table.
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >You cannot , but you set recovery mode of the database
> to
> >> SIMPLE.
> >> >Also ,consider create linked server to the .mdb and
> >> perform
> >> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> table1')
> >> >
> >> >For more details please refer to BOL.
> >> >
> >> >
> >> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> message
> >> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> >> I want to migrate my access data to sql 2000 server,
> but
> >> >> the prolems are :
> >> >> 1. my access data is very huge (hundreds thousands
> >> rows),
> >> >> can I use bcp or bulk insert to sql 2000 from ms
> >> access ?
> >> >>
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >>
> >> >> thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
the prolems are :
1. my access data is very huge (hundreds thousands rows),
can I use bcp or bulk insert to sql 2000 from ms access ?
2. Can we performs inserting data without recorded in
transaction log ?
thanks.Hundreds of thousands of rows is not an insignificant amount but likewise is
is not really an amount that should caue you concern.
A number of methods to move the data
1. Export to text file from Access and import using either BULK INSERT or
bcp
2. DTS
3. Access upsizing wizard.
4. Linked server to Access
All things are recorded in the log but to varying amounts. Look up RECOVERY
MODELs in BOL.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||KRESNA
For now days hundreds thousands rows i would not called very huge table.
> 2. Can we performs inserting data without recorded in
> transaction log ?
You cannot , but you set recovery mode of the database to SIMPLE.
Also ,consider create linked server to the .mdb and perform
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
For more details please refer to BOL.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||Dear friends,
I already try to use dts to import data from access to sql
2000 , performance is slow, it takes more than 5 menits,
because this is part of reporting system, I am afraid user
will complain, I try to use linkserver to retrieve data
from ms access but peformance is not good too.
My friends tell me, using dts with active X scripting is
slow, is it true ?
By the way, thans for your sugestions, I will try to put
temporary table in differents db and use simple mode
recovery, I hope performance is better.
Thanks.
>--Original Message--
>KRESNA
>For now days hundreds thousands rows i would not called
very huge table.
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>You cannot , but you set recovery mode of the database to
SIMPLE.
>Also ,consider create linked server to the .mdb and
perform
>SELECT * FROM OPENQUERY( test_Access1, 'select * from
table1')
>For more details please refer to BOL.
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> I want to migrate my access data to sql 2000 server, but
>> the prolems are :
>> 1. my access data is very huge (hundreds thousands
rows),
>> can I use bcp or bulk insert to sql 2000 from ms
access ?
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>> thanks.
>
>.
>|||Kresna
Just a guess.
Try remove all indexes on .mdb database.
Also perhaps you need to transfer data when all users went home.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> Dear friends,
> I already try to use dts to import data from access to sql
> 2000 , performance is slow, it takes more than 5 menits,
> because this is part of reporting system, I am afraid user
> will complain, I try to use linkserver to retrieve data
> from ms access but peformance is not good too.
> My friends tell me, using dts with active X scripting is
> slow, is it true ?
> By the way, thans for your sugestions, I will try to put
> temporary table in differents db and use simple mode
> recovery, I hope performance is better.
> Thanks.
>
>
> >--Original Message--
> >KRESNA
> >For now days hundreds thousands rows i would not called
> very huge table.
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >You cannot , but you set recovery mode of the database to
> SIMPLE.
> >Also ,consider create linked server to the .mdb and
> perform
> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> table1')
> >
> >For more details please refer to BOL.
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> I want to migrate my access data to sql 2000 server, but
> >> the prolems are :
> >> 1. my access data is very huge (hundreds thousands
> rows),
> >> can I use bcp or bulk insert to sql 2000 from ms
> access ?
> >>
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >>
> >> thanks.
> >
> >
> >.
> >|||I have exactly near 750.000 rows, I gathered from joining
5 tables in Ms Access before insert them to sql 2000
server, I think you are right, I must delete indexes in
destination and may created them back after inserted, is
it better ? , but sorry I cannot remove indexes in Ms
Access.
>--Original Message--
>How many rows do you have exactly ?
>What is the structure of the table?
>Removing the following from the destination should see an
increase in
>performance
>1. Indexes
>2. Triggers
>Also setting the recovery model to Simple will help.
>You will need to reapply afterwards.
>
>Exporting to text file then reimporting will I dare say
be no quicker for
>you. This as has been suggested would ideally be carried
out out of hours
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
>> Kresna
>> Just a guess.
>> Try remove all indexes on .mdb database.
>> Also perhaps you need to transfer data when all users
went home.
>> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> > Dear friends,
>> >
>> > I already try to use dts to import data from access
to sql
>> > 2000 , performance is slow, it takes more than 5
menits,
>> > because this is part of reporting system, I am afraid
user
>> > will complain, I try to use linkserver to retrieve
data
>> > from ms access but peformance is not good too.
>> >
>> > My friends tell me, using dts with active X scripting
is
>> > slow, is it true ?
>> >
>> > By the way, thans for your sugestions, I will try to
put
>> > temporary table in differents db and use simple mode
>> > recovery, I hope performance is better.
>> >
>> > Thanks.
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >KRESNA
>> > >For now days hundreds thousands rows i would not
called
>> > very huge table.
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >You cannot , but you set recovery mode of the
database to
>> > SIMPLE.
>> > >Also ,consider create linked server to the .mdb and
>> > perform
>> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> > table1')
>> > >
>> > >For more details please refer to BOL.
>> > >
>> > >
>> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> > message
>> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> > >> I want to migrate my access data to sql 2000
server, but
>> > >> the prolems are :
>> > >> 1. my access data is very huge (hundreds thousands
>> > rows),
>> > >> can I use bcp or bulk insert to sql 2000 from ms
>> > access ?
>> > >>
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >>
>> > >> thanks.
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Uri, why should I remove indexes from ms access ? , I
think it is not relevan, I agree with suggestions from
Allan and you to remove indexes in destinations (SQL 2000)
because t-sql insert will update index too.
Thanks.
>--Original Message--
>Kresna
>Just a guess.
>Try remove all indexes on .mdb database.
>Also perhaps you need to transfer data when all users
went home.
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> Dear friends,
>> I already try to use dts to import data from access to
sql
>> 2000 , performance is slow, it takes more than 5 menits,
>> because this is part of reporting system, I am afraid
user
>> will complain, I try to use linkserver to retrieve data
>> from ms access but peformance is not good too.
>> My friends tell me, using dts with active X scripting is
>> slow, is it true ?
>> By the way, thans for your sugestions, I will try to put
>> temporary table in differents db and use simple mode
>> recovery, I hope performance is better.
>> Thanks.
>>
>>
>> >--Original Message--
>> >KRESNA
>> >For now days hundreds thousands rows i would not called
>> very huge table.
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >You cannot , but you set recovery mode of the database
to
>> SIMPLE.
>> >Also ,consider create linked server to the .mdb and
>> perform
>> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> table1')
>> >
>> >For more details please refer to BOL.
>> >
>> >
>> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> message
>> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> >> I want to migrate my access data to sql 2000 server,
but
>> >> the prolems are :
>> >> 1. my access data is very huge (hundreds thousands
>> rows),
>> >> can I use bcp or bulk insert to sql 2000 from ms
>> access ?
>> >>
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >>
>> >> thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||OK I see now.
Indexes on the Source will help you get to the rows you want (Providing they
are well placed).
Is it a complicated SELECT ?
Have you tried importing all the tables to SQL Server and then use a view
over them to do the inserts ?
How long does it take to Export using Access your Access data to Text File ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:05e401c37062$8dd4df30$a301280a@.phx.gbl...
> I have exactly near 750.000 rows, I gathered from joining
> 5 tables in Ms Access before insert them to sql 2000
> server, I think you are right, I must delete indexes in
> destination and may created them back after inserted, is
> it better ? , but sorry I cannot remove indexes in Ms
> Access.
> >--Original Message--
> >How many rows do you have exactly ?
> >What is the structure of the table?
> >
> >Removing the following from the destination should see an
> increase in
> >performance
> >
> >1. Indexes
> >2. Triggers
> >
> >Also setting the recovery model to Simple will help.
> >
> >You will need to reapply afterwards.
> >
> >
> >Exporting to text file then reimporting will I dare say
> be no quicker for
> >you. This as has been suggested would ideally be carried
> out out of hours
> >
> >
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
> >> Kresna
> >> Just a guess.
> >> Try remove all indexes on .mdb database.
> >> Also perhaps you need to transfer data when all users
> went home.
> >>
> >> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> > Dear friends,
> >> >
> >> > I already try to use dts to import data from access
> to sql
> >> > 2000 , performance is slow, it takes more than 5
> menits,
> >> > because this is part of reporting system, I am afraid
> user
> >> > will complain, I try to use linkserver to retrieve
> data
> >> > from ms access but peformance is not good too.
> >> >
> >> > My friends tell me, using dts with active X scripting
> is
> >> > slow, is it true ?
> >> >
> >> > By the way, thans for your sugestions, I will try to
> put
> >> > temporary table in differents db and use simple mode
> >> > recovery, I hope performance is better.
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> > >--Original Message--
> >> > >KRESNA
> >> > >For now days hundreds thousands rows i would not
> called
> >> > very huge table.
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >You cannot , but you set recovery mode of the
> database to
> >> > SIMPLE.
> >> > >Also ,consider create linked server to the .mdb and
> >> > perform
> >> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> > table1')
> >> > >
> >> > >For more details please refer to BOL.
> >> > >
> >> > >
> >> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> > message
> >> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> > >> I want to migrate my access data to sql 2000
> server, but
> >> > >> the prolems are :
> >> > >> 1. my access data is very huge (hundreds thousands
> >> > rows),
> >> > >> can I use bcp or bulk insert to sql 2000 from ms
> >> > access ?
> >> > >>
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >>
> >> > >> thanks.
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||ok
I did not think you have already database on SQL Server. I thought you are
plane phase yet.
So , I agree you need to remove all indexes and triggres on SQL Server
database.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:04da01c37063$5f92df40$a401280a@.phx.gbl...
> Uri, why should I remove indexes from ms access ? , I
> think it is not relevan, I agree with suggestions from
> Allan and you to remove indexes in destinations (SQL 2000)
> because t-sql insert will update index too.
> Thanks.
> >--Original Message--
> >Kresna
> >Just a guess.
> >Try remove all indexes on .mdb database.
> >Also perhaps you need to transfer data when all users
> went home.
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> Dear friends,
> >>
> >> I already try to use dts to import data from access to
> sql
> >> 2000 , performance is slow, it takes more than 5 menits,
> >> because this is part of reporting system, I am afraid
> user
> >> will complain, I try to use linkserver to retrieve data
> >> from ms access but peformance is not good too.
> >>
> >> My friends tell me, using dts with active X scripting is
> >> slow, is it true ?
> >>
> >> By the way, thans for your sugestions, I will try to put
> >> temporary table in differents db and use simple mode
> >> recovery, I hope performance is better.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >KRESNA
> >> >For now days hundreds thousands rows i would not called
> >> very huge table.
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >You cannot , but you set recovery mode of the database
> to
> >> SIMPLE.
> >> >Also ,consider create linked server to the .mdb and
> >> perform
> >> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> table1')
> >> >
> >> >For more details please refer to BOL.
> >> >
> >> >
> >> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> message
> >> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> >> I want to migrate my access data to sql 2000 server,
> but
> >> >> the prolems are :
> >> >> 1. my access data is very huge (hundreds thousands
> >> rows),
> >> >> can I use bcp or bulk insert to sql 2000 from ms
> >> access ?
> >> >>
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >>
> >> >> thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Friday, March 23, 2012
How to increment ID sequentially?
I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?
Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.
|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||Hi mychucky,
You would have to use 'identity seed' property in your table design window in your SQL Server.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||
ZLA:
Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx
Thanks so much! I got it working now. I would never have found the solution if without your link.
Monday, March 19, 2012
How to improve database searhing performance?
Hi, I am using compact framework 1.1 and SQL CE database for my mobile application. My database has a total of 160000 rows of records and whenever i do a query searching, it will take about 20 seconds to look through the whole database if the record does not exist. Is there any method to improve the searching performance? i am using data reader for the query.
Thanks.
If you are using SQL statements now, consider using Seek/SetRange using an index. More information at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.seek.aspx
Sunday, February 19, 2012
How to hide subtotals if only 1 row in group?
Hello,
I have established a group so that I can provide subtotals following the presentation of the group's detail rows. I'm finding that many of the groups have only one row and my subtotals just echo what was already presented on the detail line. The overall effect is confusing to the user and a real vertical space-waster.
Can someone give me a technique or expression for hiding subtotals when the group size is 1 but showing subtotals when the group size is >= 2? If I hide the group when the group size is 1 row then the one detail row is hidden as well, no?
Thanks!
BCB
You can give your visibility the expression to count rows in that group and then False if more than 1|||But I just want to hide the subtotal line. Won't your approach hide the one detail line that belongs to the group as well as the group footer that contains the redundant subtotals?
Thanks.
|||Gotcha, another technique would be to make the font white or transparent by using the sae kind of expression....in this case you can determine exactly which text blocks you want to use
only problem is spacing issues
|||I'm, essentially, trying to do the same thing.
In my case, my report is a list of employees that can be generated for either full timers, part timers, or both.
I've set up a group on the full/part time field FT_PT. There is no group header, just a group footer.
When I generate the report for both I get:
Department Header
Full-Time Detail
FT_PT Group Footer
Part-Time Detail
FT_PT Group Footer
Department Footer
This is perfect.
When I generate it for, say, just Full-Time, I get:
Department Header
Full-Time Detail
FT_PT Group Footer
Department Footer
I don't want to see the FT_PT group footer. What I want is:
Department Header
Full-Time Detail
Department Footer
Gotta be a way to do this without having a blank line where the Full-Time Group Footer would be, isn't there?
How to hide subtotals if only 1 row in group?
Hello,
I have established a group so that I can provide subtotals following the presentation of the group's detail rows. I'm finding that many of the groups have only one row and my subtotals just echo what was already presented on the detail line. The overall effect is confusing to the user and a real vertical space-waster.
Can someone give me a technique or expression for hiding subtotals when the group size is 1 but showing subtotals when the group size is >= 2? If I hide the group when the group size is 1 row then the one detail row is hidden as well, no?
Thanks!
BCB
You can give your visibility the expression to count rows in that group and then False if more than 1|||But I just want to hide the subtotal line. Won't your approach hide the one detail line that belongs to the group as well as the group footer that contains the redundant subtotals?
Thanks.
|||Gotcha, another technique would be to make the font white or transparent by using the sae kind of expression....in this case you can determine exactly which text blocks you want to use
only problem is spacing issues
|||I'm, essentially, trying to do the same thing.
In my case, my report is a list of employees that can be generated for either full timers, part timers, or both.
I've set up a group on the full/part time field FT_PT. There is no group header, just a group footer.
When I generate the report for both I get:
Department Header
Full-Time Detail
FT_PT Group Footer
Part-Time Detail
FT_PT Group Footer
Department Footer
This is perfect.
When I generate it for, say, just Full-Time, I get:
Department Header
Full-Time Detail
FT_PT Group Footer
Department Footer
I don't want to see the FT_PT group footer. What I want is:
Department Header
Full-Time Detail
Department Footer
Gotta be a way to do this without having a blank line where the Full-Time Group Footer would be, isn't there?
How to hide specific rows in TABLE?
My report result has 100 lines of records (details) and I only want to
display the first 10 rows and hide the rest of them. How can I achieve
that?
Thanks,
KeithYou can set the visibilty property of the row based on the count of some
distinict item in the details being less than ten.
for example the expression for the visibiltiy property might read:
iif( Count( Fields!UniqueDetailItem.Value, "GroupNameIfNeeded" ) > 10,
True, False )
Hope this helps.
"Keith" wrote:
> My report result has 100 lines of records (details) and I only want to
> display the first 10 rows and hide the rest of them. How can I achieve
> that?
> Thanks,
> Keith
>|||Thanks Rand!
I tried with this expression something like:
=iif( Count( Fields!zzcusteu.Value) > 10, True, False )
But it still doesn't work. It just counted the total # of rows, if >10,
then hide all the rows (I want to the first 10 rows to be visible).
Any idea?
Thanks,
Keith|||Hi:
You can use this expression:
=iif( RowNumber( Nothing) > 10, True, False )
Since Count() return the total number of records, but RowNumber() return
the current row number
Kent
"Keith" wrote:
> Thanks Rand!
> I tried with this expression something like:
> =iif( Count( Fields!zzcusteu.Value) > 10, True, False )
> But it still doesn't work. It just counted the total # of rows, if >10,
> then hide all the rows (I want to the first 10 rows to be visible).
> Any idea?
> Thanks,
> Keith
>
display the first 10 rows and hide the rest of them. How can I achieve
that?
Thanks,
KeithYou can set the visibilty property of the row based on the count of some
distinict item in the details being less than ten.
for example the expression for the visibiltiy property might read:
iif( Count( Fields!UniqueDetailItem.Value, "GroupNameIfNeeded" ) > 10,
True, False )
Hope this helps.
"Keith" wrote:
> My report result has 100 lines of records (details) and I only want to
> display the first 10 rows and hide the rest of them. How can I achieve
> that?
> Thanks,
> Keith
>|||Thanks Rand!
I tried with this expression something like:
=iif( Count( Fields!zzcusteu.Value) > 10, True, False )
But it still doesn't work. It just counted the total # of rows, if >10,
then hide all the rows (I want to the first 10 rows to be visible).
Any idea?
Thanks,
Keith|||Hi:
You can use this expression:
=iif( RowNumber( Nothing) > 10, True, False )
Since Count() return the total number of records, but RowNumber() return
the current row number
Kent
"Keith" wrote:
> Thanks Rand!
> I tried with this expression something like:
> =iif( Count( Fields!zzcusteu.Value) > 10, True, False )
> But it still doesn't work. It just counted the total # of rows, if >10,
> then hide all the rows (I want to the first 10 rows to be visible).
> Any idea?
> Thanks,
> Keith
>
Subscribe to:
Posts (Atom)