Insert using 2 diff db's - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert using 2 diff db's

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-18 : 20:05:32

I have this insert statement where I use a function to return the field names and field type of a table. The table is in a different db than the table I am wanting to insert the values into. How can I do this without literally prefixing the db's? I would like to have variables that I can declare on top and use that, but I have not been able to do it successfully.

@TID = 1
TName = 'mytablename'

Insert Into tblFields(TableID, FieldName, FieldType)
Select @TID, ColumnName, ColumnType From PRGX_AS_UTILITY.dbo.fn_GetColumnInfo(@TName) Where @TID NOT IN(Select TableID From tblFields)

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-18 : 20:31:20

just wanted to explain something...

@TID = 1
@TName = 'mytablename'

Insert Into tblFields(TableID, FieldName, FieldType)
Select @TID, ColumnName, ColumnType From PRGX_AS_UTILITY.dbo.fn_GetColumnInfo(@TName) Where @TID NOT IN(Select TableID From tblFields)

tblFields is in database1
The function uses a table from database2 to return records with fieldname, fieldtype.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:19:52

provided both databases are on server you can use database1.dbo.table, database2.dbo.table etc

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

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-19 : 12:34:17

Getting an error msg below("Incorrect syntax near the keyword 'Where'.").

set @SysColumns = @SourceDB + '.dbo.SysColumns'
set @SysObjects = @SourceDB + '.dbo.SysObjects'

Insert Into tblFields(TableID, FieldName, FieldType)
Select @TID, ColumnName, ColumnType
From (SELECT Top 100
SysColumns.[Name] as ColumnName,
SysTypes.[Name] AS [ColumnType]
FROM
@SysObjects INNER JOIN @SysColumns --nj_toys_claims.dbo.SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = 'U'
AND SysTypes.[Name] <> 'sysname'
AND sysobjects.[name] = @TName
ORDER By SysColumns.[Name]
)
Where @TID NOT IN(Select TableID From tblFields)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:46:35

you need dynamic sql if you want use variables like this. why do you want to pass db name through variable?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources