April 8, 2002 at 7:01 pm
I am trying to come with a SQL statement to do the following.
I have two tables in a DB.
One table has user information in it while the other has personal user information like address phone number and such. What I want to do is insert the personal information from table 2 into table one. Heres where it gets tricky. Table one has two columns that make up a unique ID For instance:
col1 col2
abc 123
Table 2 has just one columns with both these values:
col1
abc123
How do I insert the information from table two into table one into there respective columns according to table 2's joined unique ID wich are represented in two columns in table one. Also I need to loop throught the records and do this automatiaclly.
Currently I am using a DTS package to get the data from two different text files. One again makes up table one and the second table two.
Any help would help
April 8, 2002 at 7:24 pm
I really don't know unless the length of table1.col1 is constant. If you know the length of table1.col1 was 3 for example you could:
insert into table1 (col1, col2)
select
left(t2.col1, 3),
substring(t2.col1, 4, len(t2) - 3)
from
table1 t
left join
table2 t2 on t.col1 + t.col2 = t2.col1
where
t.col1 is null
If not, can you get away with just a view instead of moving the data?
Select * from t join t2 on t.col1 + t.col2 = t2.col1
April 9, 2002 at 6:51 am
The sql Bombs. Its looking for two arguments in the left function. Here's what I have so far:
INSERT INTO dbo.Amisys_MMembInfo
(Street_Address1, Street_Address2)
SELECT LEFT(dbo.Amisys_MMembAddress.Family_Number) AS Expr1, SUBSTRING(dbo.Amisys_MMembAddress.Family_Number,
LEN(Amisys_MMembAddress) - 3) AS Expr2
FROM dbo.Amisys_MMembInfo LEFT OUTER JOIN
dbo.Amisys_MMembAddress ON
dbo.Amisys_MMembInfo.MemContract_ID + dbo.Amisys_MMembInfo.MemContract_Suffix = dbo.Amisys_MMembAddress.Family_Number
WHERE (dbo.Amisys_MMembInfo.Street_Address1 IS NULL)
April 9, 2002 at 6:51 am
The sql Bombs. Its looking for two arguments in the left function. Here's what I have so far:
INSERT INTO dbo.Amisys_MMembInfo
(Street_Address1, Street_Address2)
SELECT LEFT(dbo.Amisys_MMembAddress.Family_Number) AS Expr1, SUBSTRING(dbo.Amisys_MMembAddress.Family_Number,
LEN(Amisys_MMembAddress) - 3) AS Expr2
FROM dbo.Amisys_MMembInfo LEFT OUTER JOIN
dbo.Amisys_MMembAddress ON
dbo.Amisys_MMembInfo.MemContract_ID + dbo.Amisys_MMembInfo.MemContract_Suffix = dbo.Amisys_MMembAddress.Family_Number
WHERE (dbo.Amisys_MMembInfo.Street_Address1 IS NULL)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply