help tranferring data - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help tranferring data

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-09 : 11:55:21

hello,

in my dts package i need to split one table into two basically. lets assume this is my table structure

transactionid customername
------------- ------------
1 mike
2 john

when i run the dts package, i will have two tables like so:

CUSTOMERS
customerid customername
------------- ------------
1 mike
2 john

TRANSACTIONS
id transactionid customerid
-- ------------- ----------
1 1 1
2 2 2

my customerid will be autoincremented, so how do i get the customerid in the transactions table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-09 : 13:02:39

Do you need DTS for this?

INSERT INTO CUSTOMERS (CustomerName)
SELECT CustomerName
FROM Source

INSERT INTO TRANSACTIONS (TransactionID)
SELECT TransactionID
FROM Source

I've assumed id in TRANSACTIONS table is autoincrement as well.

Tara

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-09 : 14:59:55

i need to get customerid into the transactions table though.......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-09 : 15:15:14

INSERT INTO TRANSACTIONS (TransactionID, CustomerID)
SELECT s.TransactionID, c.CustomerID
FROM Source s
INNER JOIN CUSTOMERS c
ON s.CustomerName = c.CustomerName

Tara

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-09 : 15:17:05

there are thousands of records, i can't take a risk of having duplicate customers with the same name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-09 : 15:23:16

Then:

INSERT INTO CUSTOMERS (CustomerName)
SELECT CustomerName
FROM Source

INSERT INTO TRANSACTIONS (TransactionID, CustomerID)
SELECT TransactionID, 0
FROM Source

UPDATE t
SET CustomerID = c.CustomerID
FROM TRANSACTIONS t
INNER JOIN Source s
ON s.TransactionID = t.TransactionID
INNER JOIN CUSTOMERS c
ON s.CustomerName = c.CustomerName

If this isn't it, please provide DDL (CREATE TABLE statements for 3 tables) and DML (INSERT INTO SourceTable for sample data) for us to test on our machines. Sample data should reflect the problem, which means include customer name dups. Also provide expected result set using this new sample data.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources