Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gureggu
Starting Member
5 Posts |
 Posted - 2002-11-27 : 17:04:13
|
| I'm trying to populate a table with data from another table in stages, through a number of inserts. My problem is that each new insert places data on the row immediately following the last insert. Is there any way I can start this new insert on the same row as the previous one?Thanks. |
|
|
PaulTeal
Yak Posting Veteran
67 Posts |
Posted - 2002-11-27 : 18:21:05
|
| Why not use a view to sort your results? This would keep you from having to rely on the physical sort order of the data in your table. |
 |
|
|
gureggu
Starting Member
5 Posts |
Posted - 2002-11-27 : 20:15:23
|
| Actually, I'm trying to create a fact table for a star schema that has data transferred into it from the infamous Northwind database. But there's so much data that it needs to be loaded gradually from the dimension tables using a temp table for staging. Someone mentioned dropping, recreating and populating the table each time, like this: DROP TABLE TEMP SELECT TIME_KEY AS TIME_KEY INTO TEMP FROM TIME_DIM but if I do this, I don't understand how I can insert the data into the Fact table without running into the same INSERT problems as I did on the original temp table. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-27 : 21:06:43
|
| There is no concept of physical order in a relational database. More accurately, the physical order of the data is hidden from the user; it is meaningless and has no bearing on how that data is inserted or manipulated. It is not like a spreadsheet with numbered rows.The only way to guarantee that data is stored in a paricular physical order is to use a clustered index. That DOES NOT guarantee that the data will be retrieved in that physical order though. The only way to do that is to use an ORDER BY clause in your SELECT statement.Why do you need to load data gradually? Speed? The SELECT...INTO statement you're using now should work fine, as it is a minimally logged operation.Edited by - robvolk on 11/27/2002 21:08:03 |
|
|
|
gureggu
Starting Member
5 Posts |
Posted - 2002-11-27 : 21:51:13
|
| The order in which I put the data in isn't the problem. The problem is that the Fact table does not allow nulls for it's primary keys, and when I load it gradually with the temp table it leaves null values because some of the keys (which are populated in subsequent INSERTS and are therefore null in the rows where the previous INSERT occured) are null.You're correct about the reason for populating it gradually. The database is so large that the transaction log becomes locked up if I try to insert them all at once. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-27 : 22:23:57
|
| I think there are some crossed wires as to what you are actually trying to do.How about posting some DDL etc so we can see your table stucture and maybe some sample data.Damian |
|
|
|
gureggu
Starting Member
5 Posts |
Posted - 2002-11-28 : 00:41:34
|
| Okay. Sorry if my explanation was a little murky. Here's my code to create a star schema:CREATE TABLE EMPLOYEE_DIM ( EMPLOYEE_KEY INTEGER IDENTITY(1,1) NOT NULL UNIQUE, EMPLOYEE_ID INTEGER NOT NULL, LASTNAME NVARCHAR(20) NOT NULL, FIRSTNAME NVARCHAR(10) NOT NULL, TITLE NVARCHAR(30) NOT NULL, PRIMARY KEY (EMPLOYEE_KEY));CREATE TABLE SHIPPER_DIM ( SHIPPER_KEY INTEGER IDENTITY(1,1) NOT NULL UNIQUE, SHIPPER_ID INTEGER NOT NULL, COMPANY_NAME NVARCHAR(40) NOT NULL, PHONE NVARCHAR(24) NOT NULL, PRIMARY KEY (SHIPPER_KEY));CREATE TABLE CUSTOMER_DIM ( CUSTOMER_KEY INTEGER IDENTITY(1,1) NOT NULL UNIQUE, CUSTOMER_ID NCHAR(5) NOT NULL, COMPANY_NAME NVARCHAR(40) NOT NULL, CONTACT_TITLE NVARCHAR(30) NOT NULL, ADDRESS NVARCHAR(60) NOT NULL, CITY NVARCHAR(15) NOT NULL, REGION NVARCHAR(15), POSTAL_CODE NVARCHAR(10), COUNTRY NVARCHAR(15) NOT NULL, PHONE NVARCHAR(24) NOT NULL, FAX NVARCHAR(24), PRIMARY KEY (CUSTOMER_KEY));CREATE TABLE PRODUCT_DIM ( PRODUCT_KEY INTEGER IDENTITY(1,1) NOT NULL UNIQUE, PRODUCT_ID INTEGER NOT NULL, PRODUCT_NAME CHAR(40) NOT NULL, CATEGORY_ID INTEGER NOT NULL, CATEGORY_NAME CHAR(15) NOT NULL, SUPPLIER_ID INTEGER NOT NULL, DESCRIPTION NTEXT NOT NULL, COMPANY_NAME NVARCHAR(40) NOT NULL, CITY NVARCHAR(15) NOT NULL, REGION NVARCHAR(15), POSTAL_CODE NVARCHAR(10), COUNTRY NVARCHAR(15) NOT NULL, PRIMARY KEY (PRODUCT_KEY));CREATE TABLE SALES_FACT ( PRODUCT_KEY INTEGER NOT NULL, CUSTOMER_KEY INTEGER NOT NULL, EMPLOYEE_KEY INTEGER NOT NULL, TIME_KEY INTEGER NOT NULL, SHIPPER_KEY INTEGER NOT NULL, UNIT_PRICE MONEY NOT NULL, QUANTITY SMALLINT NOT NULL, DISCOUNT REAL NOT NULL, );CREATE TABLE TIME_DIM ( TIME_KEY INTEGER IDENTITY(1,1) NOT NULL UNIQUE, ORDER_DATE DATETIME NOT NULL, DAY_VALUE NVARCHAR(10), MONTH_VALUE NVARCHAR(10), QUARTER_VALUE NVARCHAR(10), YEAR_VALUE NVARCHAR(10), PRIMARY KEY (TIME_KEY) );And this is the code to populate the dimension tables:INSERT INTO EMPLOYEE_DIM(EMPLOYEE_ID,LASTNAME,FIRSTNAME,TITLE) SELECT NORTHWIND.DBO.EMPLOYEES.EMPLOYEEID,NORTHWIND.DBO.EMPLOYEES.LASTNAME, NORTHWIND.DBO.EMPLOYEES.FIRSTNAME,NORTHWIND.DBO.EMPLOYEES.TITLE FROM NORTHWIND.DBO.EMPLOYEES INSERT INTO SHIPPER_DIM(SHIPPER_ID,COMPANY_NAME,PHONE) SELECT NORTHWIND.DBO.SHIPPERS.SHIPPERID,NORTHWIND.DBO.SHIPPERS.COMPANYNAME, NORTHWIND.DBO.SHIPPERS.PHONE FROM NORTHWIND.DBO.SHIPPERS INSERT INTO CUSTOMER_DIM(CUSTOMER_ID,COMPANY_NAME,CONTACT_TITLE,ADDRESS,CITY,REGION,POSTAL_CODE,COUNTRY,PHONE,FAX) SELECT NORTHWIND.DBO.CUSTOMERS.CUSTOMERID,NORTHWIND.DBO.CUSTOMERS.COMPANYNAME,NORTHWIND.DBO.CUSTOMERS.CONTACTTITLE, NORTHWIND.DBO.CUSTOMERS.ADDRESS,NORTHWIND.DBO.CUSTOMERS.CITY,NORTHWIND.DBO.CUSTOMERS.REGION, NORTHWIND.DBO.CUSTOMERS.POSTALCODE,NORTHWIND.DBO.CUSTOMERS.COUNTRY,NORTHWIND.DBO.CUSTOMERS.PHONE, NORTHWIND.DBO.CUSTOMERS.FAX FROM NORTHWIND.DBO.CUSTOMERS INSERT INTO PRODUCT_DIM(PRODUCT_ID,PRODUCT_NAME,CATEGORY_ID,CATEGORY_NAME,SUPPLIER_ID,DESCRIPTION,COMPANY_NAME,CITY,REGION,POSTAL_CODE,COUNTRY) SELECT NORTHWIND.DBO.PRODUCTS.PRODUCTID,NORTHWIND.DBO.PRODUCTS.PRODUCTNAME,NORTHWIND.DBO.CATEGORIES.CATEGORYID, NORTHWIND.DBO.CATEGORIES.CATEGORYNAME,NORTHWIND.DBO.SUPPLIERS.SUPPLIERID,NORTHWIND.DBO.CATEGORIES.DESCRIPTION,NORTHWIND.DBO.SUPPLIERS.COMPANYNAME, NORTHWIND.DBO.SUPPLIERS.CITY,NORTHWIND.DBO.SUPPLIERS.REGION,NORTHWIND.DBO.SUPPLIERS.POSTALCODE,NORTHWIND.DBO.SUPPLIERS.COUNTRY FROM NORTHWIND.DBO.PRODUCTS,NORTHWIND.DBO.CATEGORIES,NORTHWIND.DBO.SUPPLIERS WHERE NORTHWIND.DBO.CATEGORIES.CATEGORYID = NORTHWIND.DBO.PRODUCTS.CATEGORYID AND NORTHWIND.DBO.PRODUCTS.SUPPLIERID = NORTHWIND.DBO.SUPPLIERS.SUPPLIERID INSERT INTO TIME_DIM(ORDER_DATE) SELECT NORTHWIND.DBO.ORDERS.ORDERDATE FROM NORTHWIND.DBO.ORDERS UPDATE TIME_DIM SET DAY_VALUE = DATENAME(WEEKDAY,ORDER_DATE) UPDATE TIME_DIM SET MONTH_VALUE = DATENAME(MONTH,ORDER_DATE) UPDATE TIME_DIM SET QUARTER_VALUE = DATEPART(QUARTER,ORDER_DATE) UPDATE TIME_DIM SET YEAR_VALUE = DATEPART(YEAR,ORDER_DATE); My problem comes when trying to create the fact table. Since there's so much data it has to be populated in stages, and I was told to use a temp table as a staging table, using code such as that in my previous post. My trouble comes from trying to get all of the keys in the Fact table to start on the same row, since none of them are allowed to be null. |
|
|
|
PaulTeal
Yak Posting Veteran
67 Posts |
Posted - 2002-11-28 : 01:34:53
|
| Are you trying to circumvent the primary keys with the temporary tables? If so, you may violate the data integrity of your database and thwart your database designer's intentions for the table.Paul Tealsql@partytilyoupop.com |
|
|
|
gureggu
Starting Member
5 Posts |
Posted - 2002-11-28 : 16:49:30
|
| Basically, I'm trying to build the fact table with the temp table, but I think I'm going about it in the wrong way. |
|
|
|
|
|
|
|
|
Populating a table incrementally