Inserting to a table from a CTE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Inserting to a table from a CTE?

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 13:01:31

Hello,

I have created a CTE to combine some data. I have also created a temporary table that I need this data that the CTE returns to be inserted to and then everything that is in this temp table needs to be inserted to an existing table in my database. I don't know how to select the values from the CTE into the temp table?

Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)

Does my select go here??? I tried select * and it doesn't like that syntax...

With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:07:44

[code]Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)

;
With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)
SELECT fields from labtestcheck
.....

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:09:10

b/w you temporary table contains lots of other columns whereas your CTE has only one column. Where will you get values of other fields from? You need to put them in select query for inserting into temp table.

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 13:12:16

The rows I want to insert are actually the rows that appear in the last select statment.....

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

I tried this:

Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null,sequence_number int null, table_name varchar(30) null,
field_name varchar(30) null)

With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)
select t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,row_number() over (partition by t.labtestcode order by b.valuecode) as sequence_number,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,sequence_number,table_name,field_name)

I get error
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ')'.
and it seems to be referrig to the last line where the Insert occurs

Do I need to put those somewhere else?

quote:
Originally posted by visakh16

b/w you temporary table contains lots of other columns whereas your CTE has only one column. Where will you get values of other fields from? You need to put them in select query for inserting into temp table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:17:37

quote:
Originally posted by sross81

The rows I want to insert are actually the rows that appear in the last select statment.....

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

Do I need to put those somewhere else?

quote:
Originally posted by visakh16

b/w you temporary table contains lots of other columns whereas your CTE has only one column. Where will you get values of other fields from? You need to put them in select query for inserting into temp table.

ok. then here's the full code

Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null, 
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)


;
With labtestcheck (labtestcode)
as 
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)
SELECT l.labtestcode,
t.valuecode,
t.valuedesc,
t.valuetype,
t.units,t.tablename,
t.fieldname 
FROM labtestcheck l
INNER JOIN
(
select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode)t
ON t.labtestcode=l.labtestcode

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 13:24:07

Thank you for showing me how to do that. It came up with an error though

Msg 8152, Level 16, State 2, Line 9
String or binary data would be truncated.
The statement has been terminated.

When I double click the error it brings me to the With labtestcheck(labtestcode) line at the top.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

The rows I want to insert are actually the rows that appear in the last select statment.....

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

Do I need to put those somewhere else?

quote:
Originally posted by visakh16

b/w you temporary table contains lots of other columns whereas your CTE has only one column. Where will you get values of other fields from? You need to put them in select query for inserting into temp table.

ok. then here's the full code

Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)

;
With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)
SELECT l.labtestcode,
t.valuecode,
t.valuedesc,
t.valuetype,
t.units,t.tablename,
t.fieldname
FROM labtestcheck l
INNER JOIN
(
select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode)t
ON t.labtestcode=l.labtestcode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:34:30

quote:
Originally posted by sross81

Thank you for showing me how to do that. It came up with an error though

Msg 8152, Level 16, State 2, Line 9
String or binary data would be truncated.
The statement has been terminated.

When I double click the error it brings me to the With labtestcheck(labtestcode) line at the top.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

The rows I want to insert are actually the rows that appear in the last select statment.....

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

Do I need to put those somewhere else?

quote:
Originally posted by visakh16

b/w you temporary table contains lots of other columns whereas your CTE has only one column. Where will you get values of other fields from? You need to put them in select query for inserting into temp table.

ok. then here's the full code

Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)

;
With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)
SELECT l.labtestcode,
t.valuecode,
t.valuedesc,
t.valuetype,
t.units,t.tablename,
t.fieldname
FROM labtestcheck l
INNER JOIN
(
select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode)t
ON t.labtestcode=l.labtestcode

This error is because you are trying to put a string value to a field which dont have sufficeint length to hold that. Please check the data that you're trying to insert to your temp table and change lengths of fields to accomodate them.

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 13:39:03

Ok I fixed the data type problem. Thanks for the advice. I got all my data into the temp table...now onto the next step. You have been so helpful as I learn I really appreciate it! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:43:48

quote:
Originally posted by sross81

Ok I fixed the data type problem. Thanks for the advice. I got all my data into the temp table...now onto the next step. You have been so helpful as I learn I really appreciate it! :)

You're welcome

. Feel free to post if you face any problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources