SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create Insert Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/07/2005 :  05:21:24  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO

CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO

CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO

----------------------------------

CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]

SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)

END

SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)

Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS

INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'

SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1

DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END

SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(3),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)

SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME

SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY



Kapil Arya

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/08/2005 :  01:08:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Kapil,
Did you test this script?

When a table given has identity column, it gives the error

Server: Msg 2744, Level 16, State 2, Procedure CreateInsQryEx, Line 32
Multiple identity columns specified for table 'KSINSERTQRY'. Only one identity column per table is allowed.

When a table without identity column is given, the following error occurs

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/08/2005 :  05:23:24  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
madhivanan,

i m using this script to generate insert queries in office since 1 year. yes this script fails in certain cases like

identity field or if there is " ' " in any of the string field.

Kapil Arya
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/08/2005 :  05:26:40  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
can u please supply me the script of the table u r trying to generate the insert query and some records so that i can test

Kapil Arya
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/08/2005 :  05:32:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>identity field or if there is " ' " in any of the string field.

Absolutely correct

I tested it with other table and it is working well
Also you didnot check whether the table name exists in the database
If there is image, text columns, then the result is empty

Apart from those It is really Nice script


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/08/2005 :  07:44:08  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
i created it bcaz of too much of insert queries i have to create and with column name specific insert query, i took almost 1 full day to find the logic

Kapil Arya
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 11/10/2005 :  03:22:50  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
-- Modified Version of CreateInsQry Procedure.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO

CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO

CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]

SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)

END
IF (@SelectPart = '*') --for setting if * is selected then.. specifying the column name.. :-)
Begin
Declare @SelectPart2 Varchar(8000)
Select @SelectPart2 = Coalesce(@SelectPart2 + ', ' ,' ') + '['+Column_Name + ']' From Information_Schema.Columns Where Table_Name = @TableName
Set @SelectPart = @SelectPart2
End
Print @SelectPart
SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)

Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS

INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'

SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1

DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END

SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(10),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)

SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME

SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY

Kapil Arya

Edited by - kapilarya on 11/10/2005 03:24:57
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/11/2005 :  09:58:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Here is a simpler method
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000