how can we use 'select' statement inside an 'Insert' Statement

Question

how can we use 'select' statement inside an 'Insert' Statement

tinnusqlserver

Ten Centuries

Points: 1191
More actions
May 5, 2011 at 3:16 pm

#237099

Hi Folks.... Need your suggestions.....
I have a table named 'Transfer' which contains 680 rows/records. i.e
When i execute Select Count(*) from Transfer} i will get 680 rows.
I have another Table named 'Countings' with columns as (ID Int, Row_Count_of_Transfer Int)
I had tried to use
" Insert Into Countings Values(598,Select Count(*) from Transfer)" to insert a record by dynamically counting the row count from 'Transfer' Table, I can't use '680' there. when tried, it showing me an error.
Issue: I want to insert the row count of 'Transfer' table dynamically into 'Countings' Table. How can i resolve this??
Thanks in Advance!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What is the error that you are receiving?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

you need at least one more column.. if i count everything in the table, there's just one value...in your example you said 680.

bit if you count...group by anothe rcolumn, i thin k you'd get what you are after....

for example:

SELECT

TYPE,

COUNT(*) AS TheCount

FROM sys.objects

GROUP BY TYPE

That gives me the count of tables/procs/foreign keys etc.

so you need to do the same thing in your table...what are you going to group your data by?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tinnusqlserver Ten Centuries Points: 1191 More actions · Answer 3

when trying to run " Insert Into Countings Values(598,Select Count(*) from Transfer) " i am getting the following errors :

Incorrect syntax near the keyword 'Select'.

Incorrect syntax near ')'.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

try this instead

Insert Into Countings

Select 598,Count(*) from Transfer

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MichaelJasson SSCrazy Points: 2732 More actions · Answer 5

Add one Bracket-

INSERT INTO Counting VALUES (598, (SELECT COUNT(*) FROM Transfer))

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Is ID an Identity Column?

I prefer to list the Column Names in the INSERT Statement

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

OR in this case

INSERT INTO table_name (ID, Rec_Count)

SELECT 598, COUNT (*) AS Rec_Count

FROM TRANSFER

If ID is an Identity Column you will want to use the SET INDENTITY_ON.

SET IDENTITY_INSERT table_name ON

INSERT INTO table_name (ID, Rec_Count)

SELECT 598, COUNT (*) AS Rec_Count

FROM TRANSFER

SET IDENTITY_INSERT table_name OFF

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 7

Either of the two approaches listed so far will work: there is frequently more than one way to perform any given task in SQL (but see the examples below).

The reason the extra parentheses are required around the SELECT statement in the VALUES clause is that VALUES expects a list of scalar expressions. You want to use the return value of the SELECT statement as a scalar value in your VALUES clause, and the parentheses do the necessary coercions for you.

DECLARE @T TABLE

(

data INTEGER NOT NULL,

cnt BIGINT NOT NULL

);

-- Values, with the result of a SELECT used as a scalar expression

-- (The optimizer knows that COUNT returns a single value)

INSERT @T (data, cnt)

VALUES (1, (SELECT COUNT_BIG(*) FROM sys.objects));

-- Using SELECT alone

INSERT @T (data, cnt)

SELECT

1, COUNT_BIG(*)

FROM sys.objects;

-- Error: the SELECT returns more than one value

-- The query plan includes extra operators to check this at runtime

-- (If sys.objects only had one row, it would be ok)

INSERT @T (data, cnt)

VALUES (1, (SELECT O.principal_id FROM sys.objects AS O));

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi