|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Inserting data to a table from other database. HelpHello,
I would like to insert a set of data to another table in different db as: INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) EXEC sp_MSForEachDB "use ? select name as username, '?' as dbname from sysusers where islogin = 1 and hasdbaccess = 1 ORDER by name" I am in db100 server. The query returns error. Please help me correct this or is there a way I can do it? Thanks. le9569 You didn't tell us the error message but I'm guessing that the use of
double quotes is the problem. Try this: INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) EXEC sp_MSForEachDB 'use ? select name as username, ''?'' as dbname from sysusers where islogin = 1 and hasdbaccess = 1 ORDER by name' If that's not it then please tell us the exact error message. -- David Portas SQL Server MVP -- Le9569,
Try placing the insert into the command parameter. Note that '' is 2 single quotes not a double quote. EXEC sp_MSForEachDB 'use ? INSERT INTO db88.UserAdmin.dbByUserName select name as username, ''?'' as dbname from sysusers where islogin = 1 and hasdbaccess = 1 ORDER by name' Show quote "Le9569" <Le9***@discussions.microsoft.com> wrote in message news:390E33E8-1EF6-47C0-A2B4-3C78CCD31328@microsoft.com... > Hello, > I would like to insert a set of data to another table in different db as: > > INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) > EXEC sp_MSForEachDB > "use ? > select name as username, '?' as dbname > from sysusers > where islogin = 1 > and hasdbaccess = 1 > ORDER by name" > > I am in db100 server. The query returns error. Please help me correct this > or is there a way I can do it? > > Thanks. > > le9569 Thank you. There is nothing wrong with the use command. My question is how
I write the syntax to insert data into other db: INSERT INTO db88.UserAdmin.dbByUserName SELECT * FROM dbByUserName Where nameType = 1 (dbByuserName table is in db100 and I am in there) Is the above correct syntax? I can't use that one. Thank you much for your support. le9569 Show quote "JT" wrote: > Le9569, > > Try placing the insert into the command parameter. Note that '' is 2 single > quotes not a double quote. > > EXEC sp_MSForEachDB > 'use ? > INSERT INTO db88.UserAdmin.dbByUserName > select name as username, ''?'' as dbname > from sysusers > where islogin = 1 > and hasdbaccess = 1 > ORDER by name' > > > "Le9569" <Le9***@discussions.microsoft.com> wrote in message > news:390E33E8-1EF6-47C0-A2B4-3C78CCD31328@microsoft.com... > > Hello, > > I would like to insert a set of data to another table in different db as: > > > > INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) > > EXEC sp_MSForEachDB > > "use ? > > select name as username, '?' as dbname > > from sysusers > > where islogin = 1 > > and hasdbaccess = 1 > > ORDER by name" > > > > I am in db100 server. The query returns error. Please help me correct > this > > or is there a way I can do it? > > > > Thanks. > > > > le9569 > > > That is what my revised version of the command string does. Rather than
attempting to insert the result of the exec into the table, I placed the insert within the string along with the other commands. I tested it on my end and it works. Show quote "Le9569" <Le9***@discussions.microsoft.com> wrote in message news:71115ACA-3F06-4F1B-8524-196637944E21@microsoft.com... > Thank you. There is nothing wrong with the use command. My question is how > I write the syntax to insert data into other db: > > INSERT INTO db88.UserAdmin.dbByUserName > SELECT * FROM dbByUserName > Where nameType = 1 > > (dbByuserName table is in db100 and I am in there) > Is the above correct syntax? I can't use that one. > Thank you much for your support. > le9569 > > > "JT" wrote: > > > Le9569, > > > > Try placing the insert into the command parameter. Note that '' is 2 single > > quotes not a double quote. > > > > EXEC sp_MSForEachDB > > 'use ? > > INSERT INTO db88.UserAdmin.dbByUserName > > select name as username, ''?'' as dbname > > from sysusers > > where islogin = 1 > > and hasdbaccess = 1 > > ORDER by name' > > > > > > "Le9569" <Le9***@discussions.microsoft.com> wrote in message > > news:390E33E8-1EF6-47C0-A2B4-3C78CCD31328@microsoft.com... > > > Hello, > > > I would like to insert a set of data to another table in different db as: > > > > > > INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) > > > EXEC sp_MSForEachDB > > > "use ? > > > select name as username, '?' as dbname > > > from sysusers > > > where islogin = 1 > > > and hasdbaccess = 1 > > > ORDER by name" > > > > > > I am in db100 server. The query returns error. Please help me correct > > this > > > or is there a way I can do it? > > > > > > Thanks. > > > > > > le9569 > > > > > > First of all, I truly thank you much for your help. It seems like that it is
my fault for not explaining clear enough my situation. The dbByUserName table is in the UserAdmin database, which is in the db88 server. The Account table is in the UserAccount database, which is in the db100 server. The error returns as: Incorrect syntax near db88 I do have access to both servers and their databases. Am I missing something in that query? Or is ther another way to do it? Thanks. le9569 Show quote "JT" wrote: > That is what my revised version of the command string does. Rather than > attempting to insert the result of the exec into the table, I placed the > insert within the string along with the other commands. I tested it on my > end and it works. > > "Le9569" <Le9***@discussions.microsoft.com> wrote in message > news:71115ACA-3F06-4F1B-8524-196637944E21@microsoft.com... > > Thank you. There is nothing wrong with the use command. My question is > how > > I write the syntax to insert data into other db: > > > > INSERT INTO db88.UserAdmin.dbByUserName > > SELECT * FROM dbByUserName > > Where nameType = 1 > > > > (dbByuserName table is in db100 and I am in there) > > Is the above correct syntax? I can't use that one. > > Thank you much for your support. > > le9569 > > > > > > "JT" wrote: > > > > > Le9569, > > > > > > Try placing the insert into the command parameter. Note that '' is 2 > single > > > quotes not a double quote. > > > > > > EXEC sp_MSForEachDB > > > 'use ? > > > INSERT INTO db88.UserAdmin.dbByUserName > > > select name as username, ''?'' as dbname > > > from sysusers > > > where islogin = 1 > > > and hasdbaccess = 1 > > > ORDER by name' > > > > > > > > > "Le9569" <Le9***@discussions.microsoft.com> wrote in message > > > news:390E33E8-1EF6-47C0-A2B4-3C78CCD31328@microsoft.com... > > > > Hello, > > > > I would like to insert a set of data to another table in different db > as: > > > > > > > > INSERT INTO db88.UserAdmin.dbByUserName (username, dbname) > > > > EXEC sp_MSForEachDB > > > > "use ? > > > > select name as username, '?' as dbname > > > > from sysusers > > > > where islogin = 1 > > > > and hasdbaccess = 1 > > > > ORDER by name" > > > > > > > > I am in db100 server. The query returns error. Please help me > correct > > > this > > > > or is there a way I can do it? > > > > > > > > Thanks. > > > > > > > > le9569 > > > > > > > > > > > > you're using three part instead of four; a fully-qualified object name
in SQL Server looks like the following: ServerName.DatabaseName.OwnerName.ObjectName In your case it would be db88.UserAdmin.dbo.dbByUserName OR db88.UserAdmin..dbByUserName HTH, Stu Thank you. I have done so and the same error shows.
Incorrect syntax near 'db88'. Please help. le9569 Show quote "Stu" wrote: > you're using three part instead of four; a fully-qualified object name > in SQL Server looks like the following: > > ServerName.DatabaseName.OwnerName.ObjectName > > In your case it would be db88.UserAdmin.dbo.dbByUserName > OR db88.UserAdmin..dbByUserName > > HTH, > Stu > > No. :) I completely forgot about this step. I have learn about this. Thank
you much for the hint. I think that's the main problem. Thanks, Stu. Show quote "Stu" wrote: > OK, > > Let's back up a step; has db88 been set up as a linked server? > > Stu > > np; it's easy to overlook stuff like that, and it's usally the thing I
wind up banging my head against later :)  
Other interesting topics
|
|||||||||||||||||||||||
