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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy