need some help. - 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

Transact-SQL (2000)

need some help.

Author

Topic

Tapalotapus
Starting Member

22 Posts

Posted - 2005-04-22 : 13:50:48

This is what I'm starting with.
-----------------------------------------------------------
CREATE TABLE #tbla (
[pk_tblaId] [int] IDENTITY (1, 1) NOT NULL ,
[fk_tblbId] [int] NULL,
[source] [ntext] )
insert into #tbla ( source) values('qqqqq')
insert into #tbla ( source) values('aaaaa')
insert into #tbla ( source) values('eeeee')

CREATE TABLE #tblb (
[pk_tblbId] [int] IDENTITY (1, 1) NOT NULL,
[source] [ntext] )
----------------------------------------------------------

This is what I need to do.
----------------------------------------------------------
CREATE TABLE #tbla (
[pk_tblaId] [int] IDENTITY (1, 1) NOT NULL ,
[fk_tblbId] [int] NULL,
[source] [ntext] )
insert into #tbla ( fk_tblbId, source) values(1, 'qqqqq')
insert into #tbla ( fk_tblbId, source) values(2, 'aaaaa')
insert into #tbla ( fk_tblbId, source) values(3, 'eeeee')

CREATE TABLE #tblb (
[pk_tblbId] [int] IDENTITY (1, 1) NOT NULL,
[source] [ntext] )
insert into #tblb ( source) values('qqqqq')
insert into #tblb ( source) values('aaaaa')
insert into #tblb ( source) values('eeeee')

-------------------------------------------------------------

I have 2 tables with data like this.
1. tbla
tblaId | tblbId | source
100 qqqqq
101 aaaaa
102 eeeee

2. tblb
tblbId | source

I need to get it like this.
tbla
tblaId | tblbId
100 1
101 2
102 3

tblb
tblbId | source
1 qqqqq
2 aaaaa
3 eeeee

I have two tables tbla and tblb, tbla use to store data in a field called source now it is stored in tblb and tblbs identity seed tblbId is stored in tbla as a reference.

I need to move all the existing records in tbla with a source over to tblb then get the identity seed and input it into tbla.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 15:07:40

People, to take the guess work out of helping you, please:

Post the actual DDL
ie: generate 'create' script of tables

Post the DML
ie: Create some insert statements so we can add a few sample records of your data

Then, provide the DDL and DML of expected results.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-22 : 15:24:49

Hey! That's my motto.

What's a motto?

Nothing, what's a motto with you?

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 15:34:15

It's been a long week for you, hasn't it Brett? :D

Watsa motto U? I went to Watsa Motto U! I guess that makes it my alma motto

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 15:37:33

It's taken me 600 posts to adopt that motto. I can only imagine how you feel after 6000+ !

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 17:04:18

I guess after my rant I better try to help you :) Here is one way:


alter table #tblb add pk_tblaId int

GO
insert #tblb (source, pk_tblaId)
select source, pk_tblaID
from	#tblA

Update a Set 
	a.fk_tblbId = b.pk_tblaId
From	#tbla a
JOIN	#tblb b ON a.pk_tblaId = b.pk_tblaId

GO
alter table #tblb drop column pk_tblaId

EDIT:
btw, please add to the thread rather than edit your original post. Its confusing otherwise, thanks

Be One with the Optimizer
TG

Tapalotapus
Starting Member

22 Posts

Posted - 2005-04-22 : 18:08:43

Awesome, thanks!
"please add to the thread rather than edit your original post. Its confusing otherwise, thanks" I will make sure I do that and sorry for not including the correct info to start with.

The admin should put a sticky on the forum reminding everyone to do that.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources