May 13, 2009 at 1:21 pm
I'm betting this is a newbie question that someone will have an easy answer to. I've got a database that's part of a 3rd party software package (so I don't want to change anything about the database structure), and I'm trying to import a set of data into one of its tables. The table has a PK called UserID but UserID is NOT an identity column and, of course, does NOT allow NULL values. I would like UserID to increment by one, starting at one, but that's just me and my OCD; as far as I know it doesn't matter what UserID is, as long as each entry is unique. I have a database that I want to do a SELECT FROM on and use that data to do an INSERT into the table with the UserID PK. So:
INSERT INTO 3rdPartyDatabase.dbo.Table1 --(table w/UserID PK)
(fullname, title, phone, emailaddr, dept, location, fax, employee_id)
SELECT upper(lname) + ', ' + upper(fname), substring(title,1,30), phone, email, substring(department,1,30), office, fax, [login]
FROM OtherDatabase.dbo.Table2
This isn't allowed, because UserID can't be NULL, but Table2 doesn't have a field I can use for UserID. How do I populate UserID with unique non-null values? Is there a T-SQL function for this? Do I have to write my own function? Or, :sick:, use some kind of loop?
Thanks everybody!
May 13, 2009 at 1:50 pm
Okay, this assumes that the table you are selecting from has a primary key column.
declare @maxId int -- this is the largest UserId ALREADY in the Targeted Table
select @maxId = max(id) from OtherDatabase.dbo.Table2
--create a working table to put the records together with their new id's
create table #tmpUsers (id int identity, LastName varchar(100),firstName varchar(100),keyColumnFromTable2 int,newIdent int)
insert into #tmpUsers (lastName,FirstName,keyColumnFromTable2)
select lastName,firstName from OtherDatabase.dbo.Table2
update #tmpUsers set newIdent = id +@maxId /* fix the newIdent column with the correct new values. this could be replaced if a way exists to dynamically change the seed value in the temp table identity declaration. (id int identity (@maxId,1))*/
INSERT INTO 3rdPartyDB..Table1
(fullname, title, phone, emailaddr, dept, location, fax, employee_id,UserId)
SELECT upper(lname) + ', ' + upper(fname), substring(title,1,30), phone, email, substring(department,1,30), office, fax, [login],newIdent
FROM OtherDatabase.dbo.Table2 t
join #tmpUsers on #tmpUsers.KeycolumnfromTable2 = t.KeyColumnFromTable2
drop table #tmpUsers
hope this helps.
May 13, 2009 at 1:52 pm
fyi, the last name and the firstname columns in the temp table are really not needed. i had them in there in case the table you are selecting from does not have a key column, then you could join to those and whatever other columns were needed to make a unique row.
May 13, 2009 at 1:59 pm
If you are using SQL 2005 or later you could use a ranking function:INSERT INTO 3rdPartyDatabase.dbo.Table1 --(table w/UserID PK)
(fullname, title, phone, emailaddr, dept, location, fax, employee_id, UserID)
SELECT
upper(lname) + ', ' + upper(fname),
substring(title,1,30),
phone,
email,
substring(department,1,30),
office,
fax,
[login],
ROW_NUMBER() OVER (ORDER BY upper(lname) + ', ' + upper(fname)) AS UserID -- NOTE: Can really order by anything you want
FROM
OtherDatabase.dbo.Table2If you have to do this repeatedly, you can add a MaxID to the ROW_NUMBER. Silimar to what john did. EDIT: Gah, just realized this was the SQL 7/2000 forum.. Please ignore my solution.. One other option you might be able to use is the INSERT INTO using the IDENTITY function..
May 13, 2009 at 2:23 pm
Thanks everybody, very helpful! 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply