Inserting value from one DB to another.

Question

Inserting value from one DB to another.

Mitch2007

SSC Eights!

Points: 830
More actions
August 26, 2010 at 12:46 pm

#226939

Hi all,
would love some guidance on what I thought would be a simple insert statement.
I'm inserting some values that are created on the fly but the third value is actually pulled from another table from another Databse (but on the same server). I'm sure the spelling is correct (wouldn't be the first time) and the SQL error states it is the SELECT line that is throwing me off.
I have tried the insert without the third field and it runs fine so i'm certain this is where it trips up.
Is my syntax not correct? I could insert a duff value then use an update to correct it but isdrally i'd like this achieved in one hit.
Any advice please?
DECLARE @FValue varchar(50)
SET @FValue = SELECT count(SomeField) FROM ADifferentDatabase.dbo.SomeTable
INSERT INTO AnotherDB.dbo.AnotherTable
(
Field01, Field02, Field03, Field04
)
SELECT 'AValueFor01' AS Field01, 'AValueFor02' As Field02, @FValue AS Field03, 'AValueFor04' AS Field04
GO
Cheers,
Vega...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

Mitch2007 (8/26/2010)
Hi all,
would love some guidance on what I thought would be a simple insert statement.
I'm inserting some values that are created on the fly but the third value is actually pulled from another table from another Databse (but on the same server). I'm sure the spelling is correct (wouldn't be the first time) and the SQL error states it is the SELECT line that is throwing me off.
I have tried the insert without the third field and it runs fine so i'm certain this is where it trips up.
Is my syntax not correct? I could insert a duff value then use an update to correct it but isdrally i'd like this achieved in one hit.
Any advice please?
DECLARE @FValue varchar(50)
SET @FValue = SELECT count(SomeField) FROM ADifferentDatabase.dbo.SomeTable
INSERT INTO AnotherDB.dbo.AnotherTable
(
Field01, Field02, Field03, Field04
)
SELECT 'AValueFor01' AS Field01, 'AValueFor02' As Field02, @FValue AS Field03, 'AValueFor04' AS Field04
GO
Cheers,
Vega...

First, I see that you're putting an int into a varchar(50) field. It will work, but...

What is the exact error message you're getting?

Is AnotherDB.dbo.AnotherTable.Field03 an identity column?

Can you provide the script that creates AnotherDB.dbo.AnotherTable?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

You need to put parentheses around your select statement:

DECLARE @FValue varchar(50)

SET @FValue = SELECT count(SomeField) FROM ADifferentDatabase.dbo.SomeTable

Should be:

DECLARE @FValue varchar(50);

SET @FValue = (SELECT count(SomeField) FROM ADifferentDatabase.dbo.SomeTable);

Or, you can change this to select into the variable, as in:

DECLARE @FValue varchar(50);

SELECT @FValue = count(SomeField) FROM ADifferentDatabase.dbo.SomeTable;

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Mitch2007 SSC Eights! Points: 830 More actions · Answer 3

THANK YOU Jeffrey, that worked like a charm !!

I had a feeling the syntax was awry somewhere.

Cheers!