February 12, 2015 at 4:22 am
Hey
I am having a table say Test.
Now i want to do is insert any two values in first 2 columns and rest columns should contain Hello values without considering Create Table statement.
Size of my table is not fixed.my table can contain 10 columns,20 columns or 30 columns etc...
I want to write a query that will insert 2 values into 2 columns and Hello into all other columns using sql query no stored procedure.
Suppose I have 100 columns then I want 10 and 20 values to be inserted into col1 and col2 and Hello into all other 98 columns.
Likewise if I have 200 columns then I want 10 and 20 values to be inserted into col1 and col2 and Hello into the other 198 columns.
How to write this query???
February 12, 2015 at 4:47 am
maria.pithia (2/12/2015)
HeyI am having a table say Test.
Now i want to do is insert any two values in first 2 columns and rest columns should contain Hello values without considering Create Table statement.
Size of my table is not fixed.my table can contain 10 columns,20 columns or 30 columns etc...
I want to write a query that will insert 2 values into 2 columns and Hello into all other columns using sql query no stored procedure.
Suppose I have 100 columns then I want 10 and 20 values to be inserted into col1 and col2 and Hello into all other 98 columns.
Likewise if I have 200 columns then I want 10 and 20 values to be inserted into col1 and col2 and Hello into the other 198 columns.
How to write this query???
will every insert you do require "Hello" for the other columns or will another insert require all other columns to be "Goodbye" for example ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 12, 2015 at 4:50 am
i want like this for eg:
first two columns can contain any given value or you can take any value but only first two columns and rest all columns except first two columns should contain Hello Value.
insert into Test values(10,20,hello,hello,hello...............)
February 12, 2015 at 4:55 am
is it as simple as setting the default value for all the other columns to 'Hello" (one off job)...then you wont have to worry...just insert values for first two columns and all the others will be 'Hello'
??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 12, 2015 at 4:59 am
Please see my Question as i have clearly stated that i dont want to use create table statement or dont want to set defualt value.
i want to do this sql query and i think i need to write dynamic sql query.
February 12, 2015 at 5:59 am
maria.pithia (2/12/2015)
Please see my Question as i have clearly stated that i dont want to use create table statement or dont want to set defualt value.i want to do this sql query and i think i need to write dynamic sql query.
No problem. Interesting requirement, can you tell us a little more about it?
What data type are the "first two columns"?
What data type are the rest of the columns?
How many rows will you be inserting at a time and where will the values come from for the "first two columns"?
Why use "Hello" instead of NULL or 'n/a'?
Why bother using more than two columns if any additional columns will always contain "Hello"?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 8:28 am
just a thought....maybe something along these lines
--CREATE TABLE [TEST](
--[col1] [varchar](5) NULL,
--[col2] [varchar](5) NULL,
--[col3] [varchar](5) NULL,
--[col4] [varchar](5) NULL,
--[col5] [varchar](5) NULL,
--[col6] [varchar](5) NULL,
--[col7] [varchar](5) NULL,
--[col8] [varchar](5) NULL,
--[col9] [varchar](5) NULL,
--[col10] [varchar](5) NULL
--)
DECLARE @sql nvarchar(max) = 'Insert into Test Values (';
SELECT
WHEN COLUMN_NAME = 'Col1' THEN '''Bye'''
WHEN COLUMN_NAME = 'Col2' THEN '''Adios'''
ELSE '''Hello'''
END + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test';
--remove trailing blank below
SELECT @sql = LEFT(@sql , LEN(@sql) - 1) + ')';
--PRINT @sql;
EXEC Sp_executesql @sql;
SELECT * FROM Test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 12, 2015 at 10:39 pm
Thank you so much for the ans.
but wont be it like as becasue we dont know the number of columns in our table.we only know the name of out table.so first of all you must count the number of columns from out table and after that your logic would proceed.
February 13, 2015 at 2:50 am
maria.pithia (2/12/2015)
Thank you so much for the ans.but wont be it like as becasue we dont know the number of columns in our table.we only know the name of out table.so first of all you must count the number of columns from out table and after that your logic would proceed.
did you try the code ?
change the number of columns in test table and run the code again.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2015 at 3:37 am
Hey
Yup i have tested the code and it is running perfectly and giving the expected ouput.everything is fine but what i am saying is you have fixed the number of columns.Now assume that you are not knowing the number of columns in the table then????
February 13, 2015 at 3:43 am
you dont need to know the number of columns in the table...the code will create a value for each column...be that 3 cols or 30
does this help.......create the tables if necessary....
--CREATE TABLE Test (
-- Value1 varchar(30) not null,
-- Value2 varchar(30) not null,
-- Value3 varchar(30) not null,
-- Value4 varchar(30) not null,
-- Value5 varchar(30) not null,
-- )
-- Required result: INSERT INTO Test Values ('1','Computer','hello','hello','hello')
DECLARE @sql nvarchar(max) = 'Insert into Test Values (';
SELECT
@sql = @sql + CASE
WHEN COLUMN_NAME = 'Value1' THEN '''1'''
WHEN COLUMN_NAME = 'Value2' THEN '''Computer'''
ELSE '''Hello'''
END + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test';
SELECT @sql = LEFT(@sql , LEN(@sql) - 1) + ')';
PRINT @sql;
--EXEC Sp_executesql @sql;
--SELECT * FROM Test
GO
--2nd Case when 10 columns are there:
--CREATE TABLE Test1 (
-- Value1 varchar(30) not null,
-- Value2 varchar(30) not null,
-- Value3 varchar(30) not null,
-- Value4 varchar(30) not null,
-- Value5 varchar(30) not null,
-- Value6 varchar(30) not null,
-- Value7 varchar(30) not null,
-- Value8 varchar(30) not null,
-- Value9 varchar(30) not null,
-- Value10 varchar(30) not null,
-- )
-- required result : INSERT INTO Test1 Values ('1','Computer','hello','hello','hello','hello',
-- 'hello','hello','hello','hello')
DECLARE @sql nvarchar(max) = 'Insert into Test1 Values (';
SELECT
@sql = @sql + CASE
WHEN COLUMN_NAME = 'Value1' THEN '''1'''
WHEN COLUMN_NAME = 'Value2' THEN '''Computer'''
ELSE '''Hello'''
END + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
SELECT @sql = LEFT(@sql , LEN(@sql) - 1) + ')';
PRINT @sql;
--EXEC Sp_executesql @sql;
--SELECT * FROM Test1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2015 at 3:57 am
Hey
Thanks for the awesome answer.Can you please explain me your code.please.
February 13, 2015 at 4:27 am
maria.pithia (2/13/2015)
HeyThanks for the awesome answer.Can you please explain me your code.please.
Ok...will try....does the following help?
--first, we can extract the column names from INFORMATION_SCHEMA.COLUMNS
SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
--We could then put them in a comma separated single row like this
DECLARE @sql nvarchar(max) = ''
SELECT
@sql = @sql + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
PRINT @sql;
--But we dont want the column names we want a value for all columns
DECLARE @sql nvarchar(max) = ''
SELECT
@sql = @sql + '''hello''' + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
PRINT @sql;
--However...we want to enter some specific values into certain columns
-- so using CASE statement below
DECLARE @sql nvarchar(max) = ''
SELECT
@sql = @sql + CASE
WHEN COLUMN_NAME = 'Value1' THEN '''1'''
WHEN COLUMN_NAME = 'Value2' THEN '''Computer'''
ELSE '''Hello'''
END + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
PRINT @sql;
--now all that is required is to add some final code to make it executable
DECLARE @sql nvarchar(max) = 'Insert into Test1 Values ('; --- this provides the start of the statement
SELECT
@sql = @sql + CASE
WHEN COLUMN_NAME = 'Value1' THEN '''1'''
WHEN COLUMN_NAME = 'Value2' THEN '''Computer'''
ELSE '''Hello'''
END + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test1';
SELECT @sql = LEFT(@sql , LEN(@sql) - 1) + ')'; --- this removes the last trailing comma becasue it is not required
--- and adds a final closing bracket tto the statement
PRINT @sql;
--EXEC Sp_executesql @sql;
--SELECT * FROM Test1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2015 at 4:31 am
Thanks you so much.this really helped and your solution worked.Thank you once again...
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply