MSSQL Server 2005, Help with Query to transfer infomation from multiple DBs

DavidMiller's Avatar, Join Date: May 2008
Newbie Member
Select id from tbl_account in Game_User DB

id is binary data

USE Billing
INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account


INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser
Im new when it comes to making SQL queries, so i need help badly.

Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.

the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.

Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.


sql-programs's Avatar
Go4Expert Member
Use the following scripts,
You didn't mention any condition to retrive "id" column from tbl_account table.If the condition is avialable then add it in where condtion.
declare @ID as binary
declare @UserNumber as binary

set @ID=(Select id from Game_User.dbo.tbl_account)

INSERT INTO Billing.dbo.tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) 
VALUES (@ID,'1','1','9','6') 

set @UserNumber=(select scope_identity())

INSERT INTO Billing.dbo.tblUserInfo 
VALUES (@UserNumber,@ID,'1','1','9','6')