December 8, 2011 at 12:39 pm
Hi,
I got a task to do.I need to insert/update/delete 400,000 new rows into Adventure Works DB table inorder to test how Proactive caching works in different scenarios.
Need to show POC(Proof of Concept)
My question is how to insert 400,000 rows at a time in a table.
Can you let me know the script to generate it.
Please do help me out.
Thanks in Advance.
December 8, 2011 at 12:48 pm
You just need 400K rows? You could build a tally table.
Basically just cross join a system table to generate as many rows as you need.
December 8, 2011 at 12:57 pm
How to do that?
I need to add rows to the Adventure works DW table.
December 8, 2011 at 1:01 pm
After adding these additional 400,000 rows, I need to check my SSAS cube whether its processed immediately or not.
So,Please let me know in this scenario.
What is the exact script to add new 400,000 rows to existing Adventureworks DW database table.
I downloaded Sample Adventureworks 2008 r2 to accomplish this task.
December 8, 2011 at 1:02 pm
So you want to add more rows to an existing table, not just create a new table and load it?
If that is the case post the DDL for the table and a few rows of sample data.
See this article for details on how to post the data.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
December 8, 2011 at 1:08 pm
Depending on how big your table is it might be easier to delete a bunch of rows, process your cube, then add them back.
Just make a copy of the data before you delete it. Do a "select into" and create a new table so you have a copy of the data.
It's quick and dirty but should work to support your testing.
December 8, 2011 at 1:08 pm
Yes Eric.
I want to add more data to the existing table.I think we need to enter data into the Fact table so that it gets reflected in the other tables.
December 8, 2011 at 1:09 pm
I'm new to SQL server.Please do help me with this
December 8, 2011 at 1:13 pm
here's one fast way: to use this code, you HAVE to knwo what table, and which columns you are going to insert.
insert into TargetTable(ColumnList)
SELECT
TOP 400000
ColumnList
FROM TargetTable
CROSS JOIN sys.columns s1
CROSS JOIN sys.columns s2
ORDER BY 1
Lowell
December 8, 2011 at 1:24 pm
insert into AdventureWorks.Production.Product(ProductNumber)
SELECT
TOP 400000
ProductNumber
FROM AdventureWorks.Production.Product
CROSS JOIN sys.columns s1
CROSS JOIN sys.columns s2
ORDER BY 1
I wrote this Query.
But its showing error saying
Cannot insert the value NULL into column 'Name', table 'AdventureWorks.Production.Product'; column does not allow nulls. INSERT fails.
The statement has been terminated.
December 8, 2011 at 1:29 pm
ColumnList was a placeholder for multiple columns, not just a single one;
I would image that table has half a dizen NOT NULL columns.
you'll have to sp_help it, understand it's structure, adnw rok on it from there
insert into AdventureWorks.Production.Product(ProductNumber,Name,OtherColumns)
SELECT ProductNumber,Name,OtherColumns
FROM.....
Lowell
December 9, 2011 at 9:55 am
Even if I'm not including null values its showing the same error
December 9, 2011 at 10:13 am
becasue you mentioned you needed to do this for a datawarehouse, I was assuming you had the ability to adapt an example. I was demoing the concept on how to generate a lot of rows, not the actual copy/paste solution.
your first step is to sp_help 'Production.Product'
and identify all columns that are not null.
all those columns must be included in your insert statement.
use a BEGIN TRANSACTION command, and test the syntax of the command you construct. you'll need to work thru each error you get individually until it works...
we cannot do much as far as concrete examples for you unless you put some effort into problem; if you were to provide the actual
CREATE TABLE Production.Product definition,a nd the actual INSERT INTO command you used, we might offer something more definitive, but most of the works on your side at the moment.
Lowell
December 11, 2011 at 3:06 am
If your requirement is to insert all those rows at a time into the concerned table, you might as well try using the BULK INSERT command.
First, BCP out the data you want to insert in to a file:
bcp DatabaseName.SchemaName.TableName out Filepath\FileName -Sservernamehere -T -n -k
Then the format file as below:
bcp DatabaseName.SchemaName.TableName format nul -n -f "\Filepath\FileName.fmt" -Sservernamehere -T -k -x
(You need to run these command on the command prompt, so make sure that its wrapped correctly i.e in a line)
Then, execute 'bulk insert' in SSMS as below:
Bulk Insert TableNameWhereYouwantToInsert
from '%aboveFilePath%\%abovefilename%'
with
(
DataFileType='Native' ,
BatchSize = 20000 ,
FormatFile='%FilePath%\%abovefilename%.fmt' ,
MaxErrors=100
)
December 11, 2011 at 3:53 am
1Ankit1 (12/11/2011)
First, BCP out the data you want to insert in to a file:
Then, execute 'bulk insert' in SSMS as below:
Will it not be double task, effort & time? :w00t:
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply