March 7, 2012 at 7:30 am
There is views(vOrder) in which there are about 10 tables.
There are about 500,000 records in this views.
I need to create a way to copy from vOrder into a table tOrder for end of users to run reports.
Before, I used the code below but it takes a long time.
Truncate table tOrder
Insert into tOrder select * from vOrder
Can someone teach me the best way to do this job?
March 7, 2012 at 7:46 am
Fasterst possible insert in T-SQL can be achieved when using SELECT INTO...
You will need to drop your table before doing so.
The next option is to batch your insert. Could you supply DDL for your tOrder please?
March 7, 2012 at 7:50 am
"Could you supply DDL for your tOrder please?"
Can you teach me more how to supply DDL?
March 7, 2012 at 8:02 am
There is "The One" here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2012 at 8:17 am
Eugene Elutin (3/7/2012)
Fasterst possible insert in T-SQL can be achieved when using SELECT INTO...You will need to drop your table before doing so.
The next option is to batch your insert. Could you supply DDL for your tOrder please?
The downside of this that this is for end users so I assume there are some permissions on this that are pretty specific. Be careful of dropping this table. You will need to script the permissions too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 8:43 am
Sean Lange (3/7/2012)
Eugene Elutin (3/7/2012)
Fasterst possible insert in T-SQL can be achieved when using SELECT INTO...You will need to drop your table before doing so.
The next option is to batch your insert. Could you supply DDL for your tOrder please?
The downside of this that this is for end users so I assume there are some permissions on this that are pretty specific. Be careful of dropping this table. You will need to script the permissions too.
It's not a downside, it's an effect of dropping table. You may no need in scripting permissions. When? For example if access to table is done via stored proc...
Even if permission needs to be reinstated, if it's done via ROLE, so it will require just one single grant statement...
March 7, 2012 at 8:46 am
Eugene Elutin (3/7/2012)
Sean Lange (3/7/2012)
Eugene Elutin (3/7/2012)
Fasterst possible insert in T-SQL can be achieved when using SELECT INTO...You will need to drop your table before doing so.
The next option is to batch your insert. Could you supply DDL for your tOrder please?
The downside of this that this is for end users so I assume there are some permissions on this that are pretty specific. Be careful of dropping this table. You will need to script the permissions too.
It's not a downside, it's an effect of dropping table. You may no need in scripting permissions. When? For example if access to table is done via stored proc...
Even if permission needs to be reinstated, if it's done via ROLE, so it will require just one single grant statement...
True not a downside, poor word choice, but definitely something the OP needs to be aware of.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 8:52 am
Sean Lange (3/7/2012)
Eugene Elutin (3/7/2012)
Sean Lange (3/7/2012)
Eugene Elutin (3/7/2012)
Fasterst possible insert in T-SQL can be achieved when using SELECT INTO...You will need to drop your table before doing so.
The next option is to batch your insert. Could you supply DDL for your tOrder please?
The downside of this that this is for end users so I assume there are some permissions on this that are pretty specific. Be careful of dropping this table. You will need to script the permissions too.
It's not a downside, it's an effect of dropping table. You may no need in scripting permissions. When? For example if access to table is done via stored proc...
Even if permission needs to be reinstated, if it's done via ROLE, so it will require just one single grant statement...
True not a downside, poor word choice, but definitely something the OP needs to be aware of.
Agree 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply