Inserting values in to table using query from DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting values in to table using query from DB

Author

Topic

empyrean
Starting Member

14 Posts

Posted - 2010-03-18 : 12:20:08

hi

i have a problem in inserting values into one table using query from another database. my query contains joins. please suggest me..here is the query which i wrote..

Insert into DB_FINAL.Gtype(Hgroup, G_ID, Tsuff, Mat)
VALUES

(SELECT DISTINCT DATABASE_TEST.INVENTORY.C_HGROUP, DATABASE_TEST.GTYPES.G_ID, DATABASE_TEST.GTYPES.C_TSUFF AS Tsuff,
DATABASE_TEST.INVENTORY.C_MAT
FROM DATABASE_TEST.DOC_ENTRY INNER JOIN
DATABASE_TEST.EXP_FBK ON DATABASE_TEST.D_ENTRY.D_ID= DATABASE_TEST.EXP_FBK.PROP_D_IDINNER JOIN
DATABASE_TEST.PED ON DATABASE_TEST.EXP_FBK.PED_ID = DATABASE_TEST.PED.PED_ID INNER JOIN
DATABASE_TEST.GTYPES ON DATABASE_TEST.PED.G_ID = DATABASE_TEST.GTYPES.G_ID INNER JOIN
DATABASE_TEST.INV ON DATABASE_TEST.PED.PED_ID = DATABASE_TEST.INV.PED_ID
WHERE (DATABASE_TEST.D_ENTRY.DOC_NAME = 'D3501A')
ORDER BY DATABASE_TEST.GTYPES.G_ID
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 12:21:15

Insert into DB_FINAL.Gtype(Hgroup, G_ID, Tsuff, Mat)
SELECT DISTINCT DATABASE_TEST.INVENTORY.C_HGROUP, DATABASE_TEST.GTYPES.G_ID, DATABASE_TEST.GTYPES.C_TSUFF AS Tsuff,
DATABASE_TEST.INVENTORY.C_MAT
FROM DATABASE_TEST.DOC_ENTRY INNER JOIN
DATABASE_TEST.EXP_FBK ON DATABASE_TEST.D_ENTRY.D_ID= DATABASE_TEST.EXP_FBK.PROP_D_IDINNER JOIN
DATABASE_TEST.PED ON DATABASE_TEST.EXP_FBK.PED_ID = DATABASE_TEST.PED.PED_ID INNER JOIN
DATABASE_TEST.GTYPES ON DATABASE_TEST.PED.G_ID = DATABASE_TEST.GTYPES.G_ID INNER JOIN
DATABASE_TEST.INV ON DATABASE_TEST.PED.PED_ID = DATABASE_TEST.INV.PED_ID
WHERE DATABASE_TEST.D_ENTRY.DOC_NAME = 'D3501A'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:21:42

this is enough


Insert into DB_FINAL.Gtype(Hgroup, G_ID, Tsuff, Mat)
SELECT DISTINCT DATABASE_TEST.INVENTORY.C_HGROUP, DATABASE_TEST.GTYPES.G_ID, DATABASE_TEST.GTYPES.C_TSUFF AS Tsuff, 
DATABASE_TEST.INVENTORY.C_MAT
FROM DATABASE_TEST.DOC_ENTRY INNER JOIN
DATABASE_TEST.EXP_FBK ON DATABASE_TEST.D_ENTRY.D_ID= DATABASE_TEST.EXP_FBK.PROP_D_IDINNER JOIN
DATABASE_TEST.PED ON DATABASE_TEST.EXP_FBK.PED_ID = DATABASE_TEST.PED.PED_ID INNER JOIN
DATABASE_TEST.GTYPES ON DATABASE_TEST.PED.G_ID = DATABASE_TEST.GTYPES.G_ID INNER JOIN
DATABASE_TEST.INV ON DATABASE_TEST.PED.PED_ID = DATABASE_TEST.INV.PED_ID
WHERE (DATABASE_TEST.D_ENTRY.DOC_NAME = 'D3501A')

no need of ORDER BY while inserting as it doesnt make sense unless you've a TOP clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:22:04

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 12:25:23

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

empyrean
Starting Member

14 Posts

Posted - 2010-03-18 : 13:42:02

Hi..Thank you all for the replies, but i am getting another error..Actually my table is in one server and i am extracting from another server database. So, do i need to build a connection first? how will this work? please help me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 13:45:50

If the database is on another server, then you can create a linked server to link the two systems together. Once the linked server exists, you then refer to the remote objects using the four-part naming convention: LinkedServerName.DatabaseName.SchemaName.ObjectName, where SchemaName is typically dbo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

empyrean
Starting Member

14 Posts

Posted - 2010-03-18 : 13:58:22

it worked..thank you...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 14:12:45

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources